cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinav
Community Champion
Community Champion

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

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

 

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

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
Community Champion
Community Champion

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")

 

 

 

StalinPonnusamy_1-1630729135104.png

 

 

@StalinPonnusamy ,

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

martinav
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Community Champion
Community Champion

@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
Community Champion
Community Champion

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

 

View solution in original post

@StalinPonnusamy ,

 

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

 

Many thanks,

Rod

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,913)