cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamlee
Level 8

Converting a date to a number like in Excel

I convert date to a number using Value function (e.g. Value(Date)).

 

Where I'm struggling is it seems it's in increment of seconds or milliseconds. Example, 6 Feb 2019 is 1,549,371,600,000. In Excel, that's 43502. 

 

It's a tad easier in Excel to predict as it's in increment of 1. PowerApps is in increment of 86,400,000. 

 

How do I make it convert the number just like in Excel? Am I doing something wrong in my formula?

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Converting a date to a number like in Excel

You're not doing anything wrong, it's just that the numeric representation of dates is different between PowerApps and Excel. In the former the date is represented as the number of milliseconds since Jan 1st, 1970, while in the latter it's the number of days since Dec 30th, 1899 (I don't know why those numbers were chosen).

If you want to get the "Excel value for a date" in PowerApps, you can use the DateDiff function:

2 + DateDiff(Date(1900, 1, 1), Date(2019, 2, 6), Days)

Notice that we cannot use the Date function to create Dec 30th, 1899 directly, as dates between 1 and 1899 have 1900 added to it (see the documentation for more details), so we choose Jan 1st 1900 and add the two days.

1 REPLY 1
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Converting a date to a number like in Excel

You're not doing anything wrong, it's just that the numeric representation of dates is different between PowerApps and Excel. In the former the date is represented as the number of milliseconds since Jan 1st, 1970, while in the latter it's the number of days since Dec 30th, 1899 (I don't know why those numbers were chosen).

If you want to get the "Excel value for a date" in PowerApps, you can use the DateDiff function:

2 + DateDiff(Date(1900, 1, 1), Date(2019, 2, 6), Days)

Notice that we cannot use the Date function to create Dec 30th, 1899 directly, as dates between 1 and 1899 have 1900 added to it (see the documentation for more details), so we choose Jan 1st 1900 and add the two days.