Convert a date into an integer number like SP DateValue()

[martinav]

Its so much easier to deal with integers than it is dates.  I have converted dates in SP into their integer value using DateValue().  I want to take a date in PA, and convert it to an integer, but DateValue() has a completely different purpose in PA.  Is there a way to convert an integer to a date, and the reverse in PA?

Thanks.

[WarrenBelz]

HI @martinav ,

When you refer to the Integer, do you mean as in Excel -  Today 4 September is 44443 (and tomorrow 44444) or simply yyyymmdd 20210903?

[StalinPonnusamy]

In Azure services, we use epoch time (sometimes called Unix time). In Powerapps we can do like

Convert current time to epoch (Need to convert to UTC)

Round(DateAdd( Now(), TimeZoneOffset(), Minutes )/1000,0)

Convert epoch back to the date

Text(Value(1630746455) * 1000,"[$-en-US]mm/dd/yyyy hh:mm")

[WarrenBelz]

@StalinPonnusamy ,

Happy to leave you with it - I am a SharePoint user.

[martinav]

Hmm, well...

@StalinPonnusamy I see what you are doing here.  So, if I wanted this in units, where I could do math on this number, and the units would be days... is this what I would do?  I would also need to do <,>,= with respect do days on these numbers without math.  

Round(DateAdd(Now()+1,TimeZoneOffset(),Days)/1000/86400,0)

Lastly, now I have this issue.  This works in PA, and DateValue() works in SP.  How would I do your function in SP?  I need to do a function in BOTH systems that will result in the same number.  So, now I have the problem in the other direction...

@WarrenBelz ?

Thanks,

Rod

[StalinPonnusamy]

Hi @martinav

We can compare most of the logic in the Date format itself. Can you please explain why do we need to convert to integer?

If we decide to keep integer then keep epoch time ticks

• Epoch is a universal time system. We get accurate result when we use it
• DateAdd(Now()+1,TimeZoneOffset(),Days) - Not sure a valid one

Way to convert the DataTime to Epoch and convert back from epoch to Datetime

Label 1 : Round(DateAdd(Now(),TimeZoneOffset(),Minutes)/1000/86400,0) --> Convert to Integer

Label 2 : Text(Value(Label1.Text) * 1000 * 86400,"[$-en-US] mm/dd/yyyy hh:mm") --> Convert Integer to Date

In SP, we are storing as an integer.

[martinav]

@StalinPonnusamy ,

First, I explained that search operations with dates will cause delegation in some cases.  So, converting a date in user input into an integer, then doing the search or filter is much more efficient with integers, esp if you are searching a date range, etc.  So, I want all of my dates in my database to have a integer form for this purpose.  In SP, I can do a calculated field based on a real date to get that number.  

Epoch is great, I just need to know how to get the same values in SP and in PA, so I can do the conversion on both sides.  I dont want to be locked into just one side to do conversions.  I want to be able to do it on both sides if the need arises.

The +1 in my label was just for me to check if the integer value incremented by one.  It will not be in the final function. 

So, in summary, I need a way to use Epoch time in both SP and PA, or a way to use DateValue() in SP and ??????? in PA to get the same value.  Same question I originally had.  

Thanks so far.  We will eventually get there.

[StalinPonnusamy · Accepted solution]

Hi @martinav

In PowerApps Convert date to integer

DateDiff(DateValue("12/31/1899"),DateAdd(Now(),TimeZoneOffset(),Minutes),Days)

result is 44445

Convert integer (44445) back to the date

"Converted Date : " & Text(DateAdd(DateValue("12/31/1899"), Value("44445"),Days),"[$-en-US] mm/dd/yyyy hh:mm")

SP convert Date to Integer

=DATEVALUE("9/6/2021")

 result is 44445

[martinav]

@StalinPonnusamy ,

Thats totally awesome!  Exactly what I was after.  Well done, sir!

Many thanks,

Rod

[kjoachim]

I need a formula for powerapps that would take the current date/time (now) and make it a serial date (integer)
What you have here looks the closest I have seen but for this formula-What is the Minutes and Days?
I am assuming that is a field/variable?

DateDiff(DateValue("12/31/1899"),DateAdd(Now(),TimeZoneOffset(),Minutes),Days