Search code examples
office365office365-appspower-automate

In Microsoft Flow how do I grab an excel column and format as time for an email alert?

I'm trying to use Microsoft Flow to watch a Sharepoint Excel document and send an email if a certain condition is true in a table.

The email successfully contains the time column but the time looks like this: 0.569444444444444 rather than a nice formatted 13:40:00.

In my email alert this expression works to grab the value though it is formatted as 0.569444444444444... 

item()?['Time Column']

When I read the documentation and try to format it nicely using this command however the Flow fails. 

formatDateTime(item()?['Time Column'],'hh:mm:ss')

What am I doing wrong? Any advice? Thanks.

Solution

  • This value 0.569444444444444 is a decimal representation for Excel time 13:40:00. For example 0.04167 is decimal representation for 1 hour, 1.0 is equivalent of 1 day.

    excel

    The point is to exctract hours, minutes and seconds from it which is not very hard but a little bit messy with built-in MS Flow functions:

    split(string(mul(variables('time_decimal'),int(24))),'.')[0] returns hour

    split(string(add(mul(int(60),sub(mul(variables('time_decimal'),int(24)),float(variables('hours')))),float(0.00000001))),'.')[0] returns minutes and so on.