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 III
Dual Super User III

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 III
Dual Super User III

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

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 III
Dual Super User III

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (959)