Unanswered

Convert serial number to date format

(0) Share

Report

Posted on by 83dons

I have a flow that takes appointment dates from a spreadsheet and sends an sms appointment reminder to a user. At the moment it appears serial number format 44307 in the sms rather than date format dd/mm/yyyy can the flow convert this rather than having to edit the original spreadsheet?

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Paulie78 8,422 Moderator on at

Like (4)

Report
Something like this:

addDays('1899-12-30', int(44307), 'dd-MM-yyyy')

In an actual flow you would probably have something like item()['Date'] instead of 44037 for example. The dynamic date value from excel basically. I've specified the date formatting there also, which you might want to change.

1 people found this reply helpful.

Was this reply helpful? Yes No
83dons 6 on at

Like (0)

Report

Please see screenshot. It's the field Appmt_Date.

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

Like (4)

Report
Following up on @Paulie78 's formula, that will only work if the date is a date only field in Excel. Serial dates often include decimal values to represent partial days (time). Since Flow's INT() function can't handle decimal whole numbers I normally recommend using a more involved formula that will convert from decimal whole numbers as well as integers. Yours should be something like this:

addseconds('1899-12-30',int(formatnumber(mul(float(items('List_rows_present_in_a_table')['appmt_Date']),86400),'0’)))

1 people found this reply helpful.

Was this reply helpful? Yes No
83dons 6 on at

Like (0)

Report

How do i do that given the setup above. Totally new to flows. The spreadsheet has two date fields which look OK and are in date format but flows must be reading them in as text. One date field is whole numbers the main one and the other has decimal places once you convert to text in excel.

Was this reply helpful? Yes No
83dons 6 on at

Like (0)

Report

Something wrong in the wording

Was this reply helpful? Yes No
Verified answer

Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

The items('List_rows_present_in_a_table')['appmt_Date'] was my approximation of the field you are using. If you hover over the AppmtDate field in your current email the tool tip will show you what it should be.

Was this reply helpful? Yes No
nmasmo 91 on at

Like (0)

Report

Useful if you're working with Date only field.

Was this reply helpful? Yes No