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.
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.
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.
- How to embed Image inline in Email Body using Microsoft Graph
- Responding to an Office 365 event invite via REST
- Office 365 Exchange Online permission is not visible for registered application in azure active directory
- S/MIME Email Sent via Python and Microsoft Graph Works in Gmail but Not in Outlook
- How to create event by MS Graph with room as location?
- Extract Value from Array in Power Automate
- The SMTP server requires a secure connection or the client was not authenticated.(535 5.7.3)?
- Run VBA on smartphone
- How to assign cell values to an array with named arguments
- Difference between running a userform in Visual Basic Editor (F5) and opening it with Show?
- InefficientFilter error on Microsoft Graph requests which were previously working
- Set shape / inlineshape as Decorative in Word
- MailKit OAuth2 SMTP Office365 Send Mail
- Pass multiple PrincipleIDs to the roleassignments/addroleassignment endpoint inside SharePoint Online REST API
- Microsoft Graph filter for onPremisesExtensionAttributes
- how can I compare to texts and extract only the difference text into the other cell
- SOAPUI 5.3.0 calls to online Exchange Web Services fail with 401 Unauthorized
- I want to skip showing the message when calling Office.context.ui.displayDialogAsync() method
- How do i count items in a multi item drop down cell
- Power BI to users without Power BI
- Highlight cell in first column if row contains a partial text match
- Fetch a file from Office365 sharepoint graph api with a sharing link
- Send emails using Office365 integrated in laravel
- Outlook calendar don't render with FullCalendar
- Jenkins & Email Extension Plugin - Sending email with office365 SMTP server, 501 5.5.4 Invalid Domain Name
- Microsoft Graph: List all users and their groups in one request
- Unable to define the sites for an App Registration for SahrePoint with Site.Selected
- Delete entry from the spreadsheet and move the particular data field to other spreadsheet
- invalid_request: The provided value for the input parameter 'redirect_uri' is not valid
- Office JavaScript API: track changes