Unanswered

Flow not reading data as date

(0) Share

Report

Posted on by JohnB2

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

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report
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.

Was this reply helpful? Yes No
JohnB2 71 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
JohnB2 71 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
JHS2019 30 on at

Like (0)

Report

Can you please show what you did to solve it 🙂

Was this reply helpful? Yes No