Skip to main content

Notifications

Power Apps - Building Power Apps
Suggested answer

Patch date as a number to Excel number column

Posted on 22 Nov 2024 14:08:36 by 279
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.
  • ronaldwalcott Profile Picture
    ronaldwalcott 734 on 22 Nov 2024 at 18:36:26
    Patch date as a number to Excel number column
    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.
  • Suggested answer
    Rayon Robert Profile Picture
    Rayon Robert 4 on 22 Nov 2024 at 17:36:20
    Patch date as a number to Excel number column
    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 :)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

October 2024 Newsletter…

October 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #4 How to Conntact Support…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,111

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,795

Leaderboard