Microsoft Flow - Convert AND Format DateTime from SharePoint for Australia

Using DateTime fields from SharePoint can be a royal PITA.  Here's the simplest way I've found to do a quick conversion from UTC time to AUS time.

What
Using Flow, when you pull dates from a SharePoint list, even if they look right in SharePoint, they come out wrong in Flow.

Why
SharePoint Lists store DateTime values in UTC format behind the scenes in the database.  This means even if you have Regional Settings set up in SharePoint to show the correct DateTime in the list, when you pull the data out using other applications, it spits out in UTC.

We want to fix that.

How
The expression I use to quickly convert the timezone AND format it to the correct dd/mm/yyyy format is ConvertTimeZone().

This function expects the following information:
ConvertTimeZone(DateTimeString, SourceTimeZone, DestinationTimeZone, Format)

Here's an example I've used in an Email Action in Flow:
convertTimeZone(triggerBody()?['DateTimeFromSharePoint'],'UTC','AUS Eastern Standard Time','dd/MM/yyyy HH:mm')

Here's a screenshot of the same expression used in Flow:


If you haven't used Expressions in Microsoft Flow before, you don't have to manually type everything, you can start typing the expression, then click back to the 'Dynamic Content' tab, and select the field you wish to modify, then go back to the 'Expression' tab.

If you need to convert from UTC to a timezone other than Australian Eastern Standard Time, check out the following list from Microsoft: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones

Comments

  1. As a total newbie to stings, expressions, flow etc this is gold. Thanks so much.

    ReplyDelete

Post a Comment

Popular posts from this blog

Export Group Membership From Active Directory Using Power Query

SharePoint - Field type [FieldName] is not installed properly. Go to the list settings page to delete this field.

Office 365 Groups - Quickly find the GUID of a O365 Group