cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tiffany
Helper I
Helper I

Converting Time

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. Flow Time Formatting Issue.jpg

Flow.jpg

8 REPLIES 8
yashag2255
Dual Super User II
Dual Super User II

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!

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

yashag2255
Dual Super User II
Dual Super User II

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!

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
Dual Super User II
Dual Super User II

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!

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

2019-11-06_21-33-27.jpg2019-11-06_21-34-05.jpg

yashag2255
Dual Super User II
Dual Super User II

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. 

ezgif.com-video-to-gif (1).gif

 

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!

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

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,539)