Converting Time

[tiffany]

I have a flow that pulls a start time, end time and total hours from an excel table. I can get it to pull the data and it format's it nicely into an HTML table in the email. However I can't get the start/end time formatted to a time format. I also can't get the total hours to be formatted nicely. I've tried everything. Any help would be greatly appreciated.

[yashag2255]

Hey @tiffany 

This is a known issue with excel. The time/ date type fields return an integer number in Flow. 2 potential fixes for this:

1. Select the entire column in the excel table and change the datatype to plain simple text. 

2. You can refer to a few responses here for converting the integer to a meaningful date/ time string: https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[tiffany]

Maybe I'm missing something but this still doesn't help with the time?  All these articles are about date.  

[yashag2255]

Hey @tiffany 

It should be the same thing with time too (as long as it is formatted as in the expressions). Can you try converting the column to a plain text and check if you continue to face the same issues?

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[tiffany]

First off, thank you for your help with this!  I reformatted excel to text but the same thing is happening. I think I need assistance formatting that item in the flow to hh:mm.  Can you help with that?

Right now the select items are:

item()?['EarliestArrival']

item()?['LatestDeparture']

item()?['Rounded']

I can reformat them to a date but I'm not having any luck formatting to hh:mm

[yashag2255]

Hey @tiffany 

Have you changed the formatting on both the excel tables? Ideally, if it is picking up the string value it should further update it as that only. 

And I am guessing that you are retrieving the string as hh:mm:dd AM/PM in the flow. if you just want hh:mm, you can use a compose action and use the expression 

formatDateTime(concat('1991-01-01T',first(split(outputs('Compose'),' ')),'z'),'hh:mm')

Here, instead of Outputs('Compose') you can add the element for which the conversion needs to be done. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[tiffany]

It's still not working, I'm sure it's somethign I'm doing.  

My statement is here: 

formatDateTime(concat('1991-01-01T',first(split(item()?['LatestDeparture'],' ')),'z'),'hh:mm')

Error Message here: 

The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"Employee Name": "@item()?['EmpName']",
"Date": "@variables('Yesterday')",
"WO #": "@item()?['WONum']",
"Start Time": "",
"End Time": "@{formatDateTime(concat('1991-01-01T',first(split(item()?['LatestDeparture'],' ')),'z'),'hh:mm')}@{item()?['LatestDeparture']}",
"Total Hours": "@item()?['Rounded']"
}' failed: 'In function 'formatDateTime', the value provided for date time string '1991-01-01T43774.545775463z' was not valid. The datetime string must match ISO 8601 format.'.

[yashag2255]

Hi @tiffany 

So how this works is: 

the number xxxx.yyyy that you are getting is, xxxx is the date integer from 1900-jan-1 and yyyy is the percentage of day that has completed from 00:00:00 AM. more clarity here: http://www.cpearson.com/excel/datetime.htm

Now, in the error screenshot you shared, you are getting the date.time and you will first have to split that and extract the date from the first part of the string that is using the expression as showed here: https://powerusers.microsoft.com/t5/Using-Flows/Excel-table-add-date-to-email-help/m-p/398402#M9916

And then you need to convert that percentage to hh:mm etc. that is here: https://stackoverflow.com/questions/55071744/in-microsoft-flow-how-do-i-grab-an-excel-column-and-for...

And I found an easier way too. You format the excel column as time itself and when you put that 0.yyyy over there, it automatically converts it to time. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-litu-msft]

Hi @tiffany,

Has your problem been solved?
If your problem has been solved, You could go ahead and mark @yashag2255's post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best Regards,
Community Support Team _ Lin Tu