Working with dates in Microsoft Flow

Arjan Cornelissen
flow, office-365, sharepoint-online
Page content

In my journey of using Microsoft Flow, I had some frustration this week on working with dates. The problem we had is as follows, with the sending of the emails, the dates are in UTC. To make it usable, we needed to convert it to our time zone.

Convert date and time to a time zone

For this Microsoft has built-in functions like “convertFromUtc”, this function converts the input date from UTC like a SharePoint date to any time zone. convertFromUtc(triggerBody()?[‘mydate’],‘W. Europe Standard Time’,‘dd-MM-yyyy H:mm’) this will convert the time to UTC+1. The input uses the default ISO 8601 format https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#the-round-trip-o-o-format-specifier and to find the correct name you can take a look here https://support.microsoft.com/en-us/help/973627/microsoft-time-zone-index-values

Using “convertFromUTC” works great for mandatory fields, but when a field was optional this function gives an error and the flow stopped working

Optional conversion

For the fields that where optional we had to make a workaround, only to convert if the field has a value. At first, we just added an if statement in the expression with a check if the field was ’null’ and we still had the same error. We converted this expression to use the “if” condition and that worked, but doing that for every date made the flow less readable.

So, we searched the internet for a solution and after trying a few out we had this solution

  1. Create a variable action of the type “Initialize variable”

  2. Add the following expression

    if(empty(triggerBody()?[‘MyDate’]),’’,convertTimeZone(coalesce(triggerBody()?[‘MyDate’],‘01/01/1901’),‘UTC’,‘W. Europe Standard Time’,‘dd-MM-yyyy H:mm’))

Let’s break this expression down

empty(triggerBody()?[‘MyDate’]) Check if the date has no value, this will give true or false back to the if statement it is in

When the expression above is empty then do nothing otherwise do the following expression: convertTimeZone(coalesce(triggerBody()?[‘MyDate’],‘01/01/1901’),‘UTC’,‘W. Europe Standard Time’,‘dd-MM-yyyy H:mm’)

This expression converts the date converts to the specified time zone and formats it.

In the expression above there is also the function “coalesce”, without this the expression gives an error. The coalesce returns the first non-null value, so when the field is empty, it takes the ‘01/01/1901’ and converts that. The value here must be in a date format; an empty string does not work

At first, this did not make any sense to me, the if statement should take care not getting into the else part, but it seems that Microsoft flow always executes the whole expression.

Date action

For some of the expressions, there are also actions available

See a full post on how this works here https://flow.microsoft.com/en-us/blog/working-with-dates-and-times/