cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tiffany
Level: Powered On

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

Re: Converting Time

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
Level: Powered On

Re: Converting Time

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

Dual Super User
Dual Super User

Re: Converting Time

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
Level: Powered On

Re: Converting Time

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

Dual Super User
Dual Super User

Re: Converting Time

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
Level: Powered On

Re: Converting Time

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

Dual Super User
Dual Super User

Re: Converting Time

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!

Community Support Team
Community Support Team

Re: Converting Time

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,405)