cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnB2
Frequent Visitor

Flow not reading data as date

Hi, I'm having an issue trying to create a flow. So basically its: email is received>flow extracts certain data from email>inserts data into table in excel>gets data from that row in excel>create an outlook event in calendar with that data.

 

I have everything working, except for the date. The flow is supposed to create the event with the specific date that's in the cell, relating to the row data that was pulled. This date will be different every time. I have tried loads of suggestions on these forms but I cannot seem to get it to work. When the date data is pulled into the flow its coming up as "44313"(this value will be different depending on the date that's in the cell).

 

I cant seem to figure out, how to convert the cell data into a format that the flow can read as a date. Any help much appreciated 🙂 

flow build.JPG Flow fail.JPG

  

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User
Dual Super User

The date you are seeing is called a serial date. Its the number of days (decimal is partial days for time) since 1/1/1900.  To convert it to a regular date use the following formula.

addseconds('1899-12-30',int(formatnumber(mul(float(ExcelSerialDate),86400),'0’)))

You addseconds to 12/30/1899 because 1/1/1900 is a serial date of one and 1900 isn't a leap year so if you work from 1/1/1900 you'll be off by 2.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

4 REPLIES 4
Pstork1
Dual Super User
Dual Super User

The date you are seeing is called a serial date. Its the number of days (decimal is partial days for time) since 1/1/1900.  To convert it to a regular date use the following formula.

addseconds('1899-12-30',int(formatnumber(mul(float(ExcelSerialDate),86400),'0’)))

You addseconds to 12/30/1899 because 1/1/1900 is a serial date of one and 1900 isn't a leap year so if you work from 1/1/1900 you'll be off by 2.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
JohnB2
Frequent Visitor

Thanks, for that. But its stating the expression is invalid. I assume im to convert the cell data into a string variable? Then use the function you have suggested in a compose function and replace "ExcelSerialDate" with the output of the compose function?

Or am I totally wrong?😵

 

addseconds('1899-12-30',int(formatnumber(mul(float(ExcelSerialDate),86400),'0’)))

 

Pstork1
Dual Super User
Dual Super User

That is correct, but don't translate it into a text string.  Leave it as a number.  ExcelSerialDate is a placeholder for whatever data column you are trying to convert.  In your case that is starttime and endtime, but I don't know what the JSON is for that in your particular flow.  You need to substitute the dynamic content values for those serial dates into the formula I gave you.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
JohnB2
Frequent Visitor

BINGO!!!

Got it working😊. I wasn't inputting the variable name correct into the expression. When I switched back to dynamic to select the variable name it worked. Your a genius.

 

For setting up the end date in the calendar I just used the same formula with 86401 instead. its an all day event so, it just needed to be any value after the start date.

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.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,387)