You’re offline. This is a read only version of the page.
30
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.
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
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-format-as-time-for-an-email
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!
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.'.
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
Share
Report
All responses (
Answers (
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!
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
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!
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!
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,999
Most Valuable Professional
