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:
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
As a total newbie to stings, expressions, flow etc this is gold. Thanks so much.
ReplyDeleteThanks for the comment, keep Flowing !
Delete