“My Power Automate report contains SharePoint date&time field, but the time is off by a few hours, how can I display the right time (in my time zone)?”
When you work with date&time from SharePoint in your flow, you might notice that the time doesn’t seem right. In SharePoint it’ll show you the correct time, but when used in Power Automate it’ll be off by a few hours (unless you live in the UTC time zone). The problem is that the time in SharePoint is always stored in the UTC time zone on the background. It’s converted to your local time zone only because of the SharePoint site regional settings. It’ll take the UTC time and convert it accordingly.
But this conversion doesn’t happen when you use the time in Power Automate. In Power Automate it’ll always be the UTC time, and you must convert it yourself into your local time zone. And this post will be about this conversion.
Convert time zone action
If it’s a single value to convert, you can use the ‘Convert time zone’ action. Enter the date&time from SharePoint to convert, the source is always UTC, and the destination is your local time zone. To format the final string you can use one of the options from the dropdown menu, or you can define your own format.
ConvertFromUTC(…) expression
But if it’s multiple values to convert at the same for further processing, e.g. to include the time in an HTML table, you’ll need an expression. It’s the same approach as when processing multiple people picker or multiple choice columns. Using the ‘Create HTML table’, ‘Create csv table’ or ‘Select’, you can’t preprocess the data in any action. Everything must be done in the action directly using more or less complex expressions.
And that’s where the ConvertFromUTC(…) expression comes into play as all date&time in SharePoint is stored in UTC time zone.
convertFromUtc(date, 'destinationTimeZone', 'format')Since the action has already UTC in the definition, you don’t need to enter the UTC again as a parameter. The only parameters you need is the date&time, the destination time zone, and the format to return. The date is clear, the date formatting was already described in a previous post, the only missing part is the destinationTimeZone. All the available time zones are listed here.
As an example, if I want to convert the created date/time in HTML table to my time zone, I’ll use the expression below.
convertFromUtc(item()?['Created'], 'Central Europe Standard Time', 'yyyy-MM-dd HH:mm')
Note: if the date&time dynamic content is not available in the expression, you might need to enter it manually using the item()?[‘column internal name‘] expression.
Summary
There’re two approaches to convert SharePoint date&time into the right time zone within a Power Automate flow. The first one is the easier one as it’s an action, but it has limitations. You can process the dates only one by one, it’s not possible to convert multiple dates at once. You always convert one date and then you must somehow process it.
The second option with an expression is a bit more complicated, but it’s also more universal. You can use it to convert dates one by one, but also convert multiple dates at once. It’s also the only option how to convert dates in the HTML and .csv tables, and the ‘Select’ action when you’re building e.g. reporting flows.
Hi Tom
I’m in New Zealand and UTC is causing real issues for me because it causes my dates to go wrong! So I’m trying to use the convertFromUTC expression but I can’t find any information on what the names of the time zones are .. not even on your cheat sheet 🙂
Is there a resource that lists that information?
Hello Christine,
the time zone names are on this page: https://docs.microsoft.com/en-us/previous-versions/windows/embedded/ms912391(v=winembedded.11), the column ‘Name of Time Zone’. 2nd line on the 2nd page of the cheat sheet. 🙂
Hi Tom
I’m not usually that fussy but I’ve just been trying to send me the list of calendar events and we’re on daylight saving now. There’s no time zone for daylight saving and everything is an hour out. It’s my calendar. How can that even be??
What is the logic here? Does Power Automate know when we have daylight saving (obviously not)? If something is truly time sensitive, how can one manage that?
It seems odd because MS obviously converts the times in my calendar to UTC and then coverts them back to NZ Standard Time without adjusting for daylight saving.
I’m voting for your next cheat sheet to be in depth on managing all aspects of dates and times in Power Automate!
Thanks for everything you do. I think you are the most valuable Power Automate resource on the web!
Christine
Hello Christine,
thank you for the kind words.
Isn’t there some other system involved that could mess up the time? I just did a simple test to convert current date/time to NZ Standard Time:
convertFromUtc(utcNow(), ‘New Zealand Standard Time’, ‘yyyy-MM-dd HH:mm’)
and it added 13 hours as it should so there must be a problem somewhere else.
Hi,
What’s the best way to deal flows that send out reminders by using a recurrence trigger that is set to your local timezone but the data is in SharePoint? Putting in the local date in the “Filter Query” won’t work as the source is in UTC.
Cheers
Hello Daniel,
since the date in SharePoint is stored in a UTC time zone you should work with UTC timezone in the flow. I use the conversion only when storing the date somewhere as a string, e.g. in an Excel file or in an email.
This worked great for me until I came across an instance where there was no value entered yet. How do I handle a null value? Thanks
Hello John,
you’ll need the if(…) expression to check if the date is not empty, it’s the same approach as when formatting date, only this time you convert time zone: https://tomriha.com/how-to-format-a-date-that-can-be-empty-null-in-power-automate/