Displaying dates in a Create HTML Table action in Power Automate

This is a continuation from my previous WTF episode where I shared how to email a tabular list of cases that were either five most recent cases or five oldest cases of a Contact. One of the column values is the Created On field from the Case record. When you view the sent email the dates will be displayed as UTC.

As mentioned in previous episodes, Power Automate treat date and time as UTC therefore will be displayed in a UTC format when retrieving the value. The answer as always from my other WTF episodes is to convert the UTC to the desired time zone which is what I’ll cover in this WTF episode.

Two Options  

There are two options that can be used to display the date and time correctly.

1. If Contacts are in a single time zone, a single expression can be used in the 1.2 Create HTML table action in the Flow seen in my previous WTF episode. For example in New Zealand there is only one time zone. If an Organisation is sending emails to customers that are based in New Zealand and no other country this option would be appropriate. 
2. If Contacts are in multiple time zones, a few more actions are required which has been shared in previous WTF episodes. For example in Australia there are multiple time zones. If an Organisation is sending emails to customers nationwide then a single expression with a static time zone value is not valid.

Option 1 – single expression 

Since the Created On date is a UTC value the function to use is convertFromUtc. 

This function requires 

1. The property that represents the UTC value 
2. The destination time zone name which will be a string value that represents the name of a time zone (note I use the time zone names from this article)
3. The format of the date and/or time of the desired output, for example dd/MM/yyyy 

The expression will look like this 

convertFromUtc(item()?['createdon'], 'AUS Eastern Standard Time', 'dd/MM/yyyy')

This expression will convert the Created On UTC value to a date and/or time that is more understandable by the recipient. The email will now display the Created On date value using the defined time zone in the expression.

This option is suitable if the Organisation has Contacts in a single time zone because of how the string value is explicitly referenced in the expression. It is not flexible for Contacts in multiple time zones.

Option 2 – a few more actions 

In previous WTF episodes I shared how to convert a UTC value into a value of the Contact’s local time zone. 

• Episode 10 - How to identify a Contacts' time zone in Flow using CDS and Bing Maps 
• Episode 11 - Delaying a birthday email to the recipients’ local time 
• Flow Online Conference 2019 - Delaying emails to certain individuals based on their time zone 

You’ll see that the following actions is used in all three episodes which I’ll once again use in Option 2:

1. Bing Maps action 
2. HTTP action 
3. Compose action 

These three options need to be before the Create HTML table action. 

The next steps to be explained are covered in my last blog post for my Flow Online Conference session which I’ll use again for this WTF episode. 

1.2 Get Contact Location by address 

As seen in a previous WTF episode there is a Bing Maps action "Get location by address" that allows the location latitude and longitude to be identified based on address information. The Address 1 fields in the contact record will be used. 

1.3 Identify local time zone of Contact 

There are additional Bing Maps Time Zone APIs available which you can read from this Bing Maps blog post. These APIs are not available as Bing Maps action in Flow however the HTTP action can be used to call the APIs.

In order to find out the time in the primary contact's time zone, the "Given location coordinates, find the time zone of the place" Bing Maps API can be used through a HTTP action.

Simply reference the latitude and longitude outputs from the previous step followed by inserting a Bing Maps key.

https://dev.virtualearth.net/REST/v1/TimeZone/latitude, longitude?key=<bingmaps-key> 

1.4 Retrieve windowsTimeZoneId property 

To ensure the contact views the date and time the email in their local date we need the name of the time zone they are located in.

The windowsTimeZoneId property will have the name of the time zone which can be retrieved from the JSON response of the previous HTTP. Below is a screenshot of the response.

There are two methods which can be used to retrieve the windowsTimeZoneId property which I’ve covered previously. The Compose action will be used to reference the property without using the Parse JSON and simply an expression instead.

The expression will look something like this
body('1.3_Retrieve_local_time_of_contact')?['resourceSets'][0]?['resources'][0]?['timeZoneAtLocation'][0]?['timeZone'][0]?['windowsTimeZoneId']

1.5 Create HTML table 

The convertFromUtc function can now reference the localTime property using the output of the compose action.

The expression will be the following
convertFromUtc(item()?['createdon'], outputs('1.4_Retrieve_windowsTimeZoneID'), 'dd/MM/yyyy')

1.6 Send email to Contact 

Same set up as to the email as per the previous WTF episode and in Option One where the Created On date will be displayed in the local time zone of the Contact based on their latitude and longitude address 1 coordinates.

Summary

When dealing with date and time values where it is to be displayed to a user or a customer, it is important to remember that Power Automate treats date and time as UTC. The date and time value will need to be converted to a destination time zone which can be done using the Convert Time Zone action or several functions such as convertFromUtc as per this blog post.