Converting Excel Date Time Serial Values With Power Automate

A common issue I’ve run into and that I’ve heard others experience, is trying to use Excel DateTime values in Power Automate. You will encounter an error like below:

The reason this is an issue is due to the way Excel stores dates and times:

• Dates are stored as a number representing the number of days since 1900-01-00
• Times are stored as a fractional portion of a 24 hour day

Thus, an excel datetime value can be represented as ddddd.tttttt

For example, the current date and time is 27 July 2020 15:20:54, which in Excel is represented by the value 44039.63951.

There are a couple of additional caveats to be aware of before we try to convert the dates:

1. Excel counts 1900-01-01 as day 1 in it’s serial calculation, so you need to ensure you are counting this extra day in any date difference calculations.
2. The Excel date serial is off by 1, as it behaves as if 1900-02-29 existed. This is due to a compatibility issue with Lotus 123 back when Excel was released and is a known issue (https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year)

This means that the Excel date serial for a given day is off by 2 compared to the actual amount of days that have elapsed since 1900-01-01.

The Solution

The solution is a single compose action with the expression:

addseconds('1899-12-30',int(formatnumber(mul(float(triggerBody()['text']),86400),'0')))

This expression is a bit of a beast, so I’ve broken it down as follows:

1. Excel DateTime Serial to Float – Excel datetime serials are imported as a string value, so the first thing we need to do is convert it to a Float. For the purposes of this demo I’m using a manually triggered Flow with an input text field that I’m manually populating with the Excel datetime serial, but in a real-world implementation you would replace the triggerbody()[‘text’] value with your Excel date reference.

2. Multiply Float by 86400 – there are 86400 seconds in day, so we’re going to multiply the float value from the previous step by 86400 to calculate the amount of seconds we’re going to be adding later in the Flow.

3. formatNumber to convert Float to String – next we use a formatNumber function to convert the float output from above to a string value. Note, I’m not adding a locale parameter to the formatNumber expression as it defaults to en-US.

4. Convert String to Integer – we need an integer value to use in the addSeconds function, so we use the int function to convert the string value from above.

5. Add Seconds to 1899-30-12 – now that we have the integer value of the amount of seconds to add, we use the addSeconds function to add this to the date 1899-30-12. As indicated above, Excel DateTime serials are off by 2 days due to the caveats I outlined, therefore we add the seconds to 1899-30-12 (instead of 1900-01-01)

Conclusion

For the sake of efficiency we can combine the 5 steps above into a single Compose action using the expression I outlined at the start of the solution:

I was inspired to write this post following a tweet from Craig Porteous.

I iterated over my initial solution to come up with the above, but I am indebted to Jamie McAllister who released a video back in May that covers the same issue, so I’d recommend having a watch of that video at https://www.youtube.com/watch?v=X6Sn0RZNfyA

If you’d like to download a copy of the Power Automate Flow I created for this then click here.

Original Post https://ryanmaclean365.com/2020/07/28/converting-excel-date-time-serial-values-with-power-automate/