Display SharePoint time in the correct time zone with Power Automate
Posted On June 20, 2021
“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.
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.
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.
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.