Patch date as a number to Excel number column

(0) Share

Report

Posted on by IzzyWizz

291

Hi,

I want to patch the date and time as a number to Excel having been driven to despair by all the problems I have have had with dates.

I am using the following formula within the app for the patch:

DecimalDay: DateDiff(
            DateTimeValue("1/1/1900") - 2,
            Now(),
            TimeUnit.Days
        ) + (DateDiff(
            Now(),
            Now(),
            TimeUnit.Minutes
        ) / 1440)

The data type is a number:

The Excel column format is also number. However, when I do the patch, the number appears as 1970-01-01T00:00:45.618Z in the Excel column. The number I need is hidden in there but why isn't it just giving me 45618? How can I just get that number?

Any help would be much appreciated, thanks.

Izzy.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Rayon Robert 20 on at

Like (1)

Report

Hi @Izzy,

The issue you're facing likely arises because PowerApps handles dates and times differently than Excel, and the Patch function may not be correctly interpreting your calculated value due to mismatched data types. Here is my recommendation:

1. Use Value() or Text() function
Modify your formula slightly to explicitly convert the calculated result into a number using the Value() function.

Using Value()
DecimalDay: Value(
    DateDiff(
        DateTimeValue("1/1/1900") - 2,
        Now(),
        TimeUnit.Days
    ) + (DateDiff(
        Now(),
        Now(),
        TimeUnit.Minutes
    ) / 1440)
)

Using Text()

DecimalDay: Text(
DateDiff(
DateTimeValue("1/1/1900") - 2,
Now(),
TimeUnit.Days
) + (DateDiff(
Now(),
Now(),
TimeUnit.Minutes
) / 1440),
"[$-en-US]0.00"
)

2. Confirm that the Excel column is formatted as a number and not as a date/time type. Even though Excel can interpret numeric values as dates, mismatched formatting might lead to the output you are seeing.

Let me know if this resolves your problem :)

Was this reply helpful? Yes No
ronaldwalcott 3,847 Super User 2025 Season 2 on at

Like (0)

Report

Did you have the column previously defined as a date in the Excel spreadsheet?

If so, after changing it did you refresh the data source in the app as it is not treating the column as a number.

Was this reply helpful? Yes No
IzzyWizz 291 on at

Like (0)

Report

Hi @Rayon Robert,

It did appear to be a formatting issue somewhere. I did have the Excel column formatted as a number but I think originally, I did have it as a date. I refreshed the link in PowerApps and checked the formatting again but it still didn't work. However, when I created a new Excel column with a number format from the start, it worked first time.

Thank you for your help.

Izzy.

Was this reply helpful? Yes No