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/

20 Responses

  • Duncan Webb

    Thanks for this idea. It’s exactly what I want to do with Flows, but for MS Teams. You say “Create a variable action”. What kind did you use? I’m given 6 types of variable action to choose from including append, increment, decrement, set and initialise.

    Reply
    • Hi Duncan,

      You need the “Initialize variable”.
      I have updated the post, thanks.

      Regards,
      Arjan

      Reply
  • Nice article,
    do you have any idea how to change the language display for the date and time?
    I work in french, my authoring environment is in french, but for some reason, the dates (long date pattern) appears in English…

    Reply
  • Arturo Hernandez

    what if I wanted to use central time zone. Like kansas city, what would i enter instead of W Europe?

    Reply
  • Art Hernandez

    Im sorry but I am new to this however the link you provided is for events not items created so i don’t have the option to chose start and end in my dynamic content.

    Reply
    • Hello Art,

      No problem, create a new flow where you only put that action in it and in the test block you can see the correct name you need in your original flow.
      If that does not work, send me a message with your case and a screenshot and will take a look into it.

      Regards,
      Arjan

      Reply
  • Art Hernandez

    This is what I have https://www.screencast.com/t/nAjRYLvRT

    Reply
  • Art Hernandez

    or this one also. https://www.screencast.com/t/klzfhe6yQagn

    Reply
  • Art Hernandez

    exclude the base time on the last one sorry

    Reply
  • Art Hernandez

    This is the email that comes in https://www.screencast.com/t/acj2oKFjns

    Reply
    • Hello Art,

      As I look at this correctly the date in the approval is not shown correctly.
      The part that you circled is something Microsoft controls.
      In the “details” you can add the converted timezone variable.

      Regards,
      Arjan

      Reply
  • Art Hernandez
  • Art Hernandez

    Ok now I am able to actually view the correct time however that time that MS controls as you stated previously regarding the email… Is there any way to exclude or remove that from the email?

    Reply
    • Great.
      Not that I’m aware off

      Reply
  • Art Hernandez

    I really thank you for your help!!! Thanks Arjan

    Reply
    • No problem.
      Happy to help

      Reply
  • BB

    Hi Arjan
    Thank you very much for this post.
    I have a datetime calculated column in sharepoint and I am trying to make flow where day to day is equal that column then do something.
    I have try many things – created a question in flow forum but no body can help me here.
    I have try to convert that date, format the date but I still get error.
    the forum is here.

    when the flow reads the columns weeks as I have on my sharepoint then It read that with timezone ex. 01-01-2018 T7:00Z – I just want to remove T7:00Z and let the date be so I can run my flow.

    https://powerusers.microsoft.com/t5/General-Flow-Discussion/formatdatetime-utcnow/m-p/152190#M15522

    Thank you

    Reply
    • Hello Burim,

      Thank you, always great to hear such a thing.

      I tried some things to solve it for you.
      When you create a new action “Initialize variable” and put the following Expression “formatDateTime(triggerBody()?[‘MyDate’],’yyyy-MM-dd’)”
      Then make another action “Initialize variable” and put in here this Expression “formatDateTime(utcNow(),’yyyy-MM-dd’)”
      Now you have to strings with the format of yyyy-MM-dd and those you can compare with an “equal()” Expression.

      Hope this helps.

      Regards,
      Arjan

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.