cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Converting Date to Integer Value

I would like to take a date field form a sharepoint list from dynamic content and convert it to its integer value. I am not having any luck finding an expression that will do this. Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

I got it to work. I had to retype '1900-01-01' on my computer. 

 

So the full solution is first use initialize variable like this

 

concat(last(split(<insert date>,'/')),'-',first(split(<insert date>,'/')),'-',split(<insert date>,'/')?[1])
 
then put this variable into this expression where you want to use it
add(div(sub(ticks(<insert above date>),ticks('1900-01-01')),864000000000),2)
 
And you may have to retype '1900-01-01' on your computer as the copying and pasting did not work. 

View solution in original post

9 REPLIES 9
efialttes
Super User
Super User

Hi!

Which integer value? You mean to store it in an Excel?

So assuming you need to store today's date (2020-04-20) I would try with the following

add(div(sub(ticks('2020-04-20'),ticks('1900–01–01')),864000000000),2)

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi @Anonymous 

 

You can also checkout this article here: https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate 

 

where I have explained how to work with Date and Time type columns in Excel but the integer value part for the date and the number value part for the time would just be applicable for any date time formatted string. 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Anonymous
Not applicable

I would like to take a date in the format, 'MM/dd/yyyy' and convert it to its numeric value. So for example, 4/27/2020 would convert to 43948. Do you have an expression that could accomplish this?

Hi @Anonymous 

 

You first have to convert the date to yyyy-MM-dd format and only then use one of the formulas mentioned in the posts to convert it to the integer type. 

 

So that could be done using the split expression where you split the current date on '/' 

concat(last(split('MM/dd/yyyy','/')),'-',first(split('MM/dd/yyyy','/')),'-',split('MM/dd/yyyy','/')?[1])

 

The above expression result should be stored in a variable and then use the same in the expresssion shared earlier to get the integer. 

 

Hope this helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Anonymous
Not applicable

Okay I think I am close, but still keep getting error that time is not in iso 8601 format. This is what I have:

 

 
This is used in initialize variable, Presentation Date is pulled from SharePoint. 
Excel Date = concat(last(split(variables('Presentation Date'),'/')),'-',first(split(variables('Presentation Date'),'/')),'-',split(variables('Presentation Date'),'/')?[1])
 
And then this is used to hopefully get the numeric value but keeps failing
add(div(sub(ticks(variables('Excel Date')),ticks('1900–01–01')),864000000000),2)
 
The date I'm testing is May 4th, 2020, so it should return a result of 43955. 
 
Let me know what I should change, thanks!
 
 
Anonymous
Not applicable

I got it to work. I had to retype '1900-01-01' on my computer. 

 

So the full solution is first use initialize variable like this

 

concat(last(split(<insert date>,'/')),'-',first(split(<insert date>,'/')),'-',split(<insert date>,'/')?[1])
 
then put this variable into this expression where you want to use it
add(div(sub(ticks(<insert above date>),ticks('1900-01-01')),864000000000),2)
 
And you may have to retype '1900-01-01' on your computer as the copying and pasting did not work. 
Anonymous
Not applicable

I finally got the answer and I feel happy now to explain. I struggled a lot to convert from normal date (yyyy-MM-dd) to a integer date.

 

Step 1:

Get the desired date you want to convert into 'yyy-MM-dd' format.

I was working with current_date. So, I created a string variable named "current_date" and gave it a value using the expression :

formatDatetimeValue(utcNow(), 'yyyy-MM-dd')

Step 2:

Converting the above string variable into an integral date.

add(div(sub(ticks(<insert_date>),ticks('1900-01-01')),864000000000),2)

 

Hi @Anonymous ,

 

What power automate "actions" did you use to create the date string in step 1 and convert that string in step 2 above? 

Was this a pre-existing Dynamic Content action, or an "Expression"?   

If it was the f(x) type expression, what 'action' did you place the expression within?   Was it "initialize variable" or "compose"?     

 

I'm trying to do this, but having trouble finding the correct action to use for step 1 and 2 you mentioned.  Thanks!

I put the dynamic content for my "intake date" into the expression and it throws errors. 

 

ninjaryder1st_0-1634318046735.png

ninjaryder1st_1-1634318079004.png

 

This is great, thanks! I got it to work for me using this formula (I want to store today's date as an integer in my sharepoint list): 

 

add(div(sub(ticks(convertTimeZone(utcNow(),'UTC','Mountain Standard Time','MM/dd/yyyy')),ticks('1900-01-01')),864000000000),2)

 

Juliana Donkersgoed

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (4,040)