Time/hour not correct with Power Automate and SharePoint

When you get data from SharePoint into Power Automate and send the output to someone or another system, it can happen that the time is not correct.

We created a simple flow that gets a list item from a SharePoint list, and sends the details by email.

The first list item has time value 13:00 (or 1PM).

Within Power Automate, we get that list item and send an email.

The flow looks like this:

We saved & tested the flow and in the mailbox we see the result:

As you can see, there is a difference of two hours between the time in a SharePoint list vs email. This is not what we want.

Time in Power Automate is in UTC (Coordinated Universal Time). This means if we get a time in Power Automate, we always have to convert it to the correct time zone. I live in Brussels, so I have to convert it to UTC+1.

We have two ways of doing this, using the OOTB action in Power Automate, or writing a function.

Using the “Convert time zone” action

As source time zone, select UTC.

As destination time zone, select the time zone of your region.

Using a function to convert time

If you want to be more free, you can use the convertTimeZone() function.

There are certain scenarios in which you will be forced to use the formula. One scenario is when you retrieve multiple list items using the “Get items” action. You want to generate a table and send this table for example by email. To create the HTML table you’ll use the “Select” and “Create HTML table” action. In your select action, you’ll need this formula to convert the time. In that input field, you cannot use the out of the box action “Convert time zone”.

convertTimeZone(outputs('Get_item')?['body/Time'],'UTC','Romance Standard Time','dd/MM/yyyy HH:mm')

To convert 1 record, you can use this function in a “Compose” action. Include the output of the compose action in your e-mail or whatever action in which you send the data.

For detailed info, read the convertTimeZone() function documentation.

A list of timezone names that you can use in this function can be found in Microsoft Time Zone Index Values list. Use the value of the “Name of Time Zone” column.

Our end result shows the correct time after we converted it:

Leave a Reply

Your email address will not be published. Required fields are marked *