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
- Create a variable action of the type “Initialize variable”
-
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/
22 Responses
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.
Hi Duncan,
You need the “Initialize variable”.
I have updated the post, thanks.
Regards,
Arjan
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…
Hello Billy,
Thank you.
I did some digging for this, but it seems that you are able to set the local in Flow witch influences the formatting.
You find this under the settings button on the top right.
If that does not work I would suggest voting on this https://powerusers.microsoft.com/t5/Flow-Ideas/Localized-date-time/idi-p/125259 and https://powerusers.microsoft.com/t5/Flow-Ideas/Offer-English-UI-language-for-ALL-locales/idi-p/88298
Regards,
Arjan
what if I wanted to use central time zone. Like kansas city, what would i enter instead of W Europe?
Hello Arturo,
It is all based on the ISO 8601 format.
I could not find a quick link to the correct list.
The easiest is to use the action Convert Time Zone.
In this action place your timezone and run it. in the output, you will find the correct string.
See this discussion for screenshots https://powerusers.microsoft.com/t5/General-Flow-Discussion/Converting-UTC-to-Location-Time/td-p/75381
Regards,
Arjan
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.
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
This is what I have https://www.screencast.com/t/nAjRYLvRT
or this one also. https://www.screencast.com/t/klzfhe6yQagn
exclude the base time on the last one sorry
This is the email that comes in https://www.screencast.com/t/acj2oKFjns
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
https://www.screencast.com/t/l1KYJGGhqbKx
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?
Great.
Not that I’m aware off
I really thank you for your help!!! Thanks Arjan
No problem.
Happy to help
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
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
Hi All, I’m having a problem with converting a string into a date.
Let me explain: I use a “get row” action to pull a value from Excel. This gives me a string. I then use “Create Item” or “Update item” to write that value into a filed that has Date/Time format. I get error message that the provided string does not adhere to ISO 8601 format. I then try to transform my string into a date using formula FormatDateTime([value]) but that gives same error…. any suggestions?
Thanks
Regards
Charles
Hello Charles,
I guess here that you write it to a SharePoint list item.
SharePoint uses the iso8601 format like the error message states. This is “yyyy-MM-ddThh:mm:ssZ” and you can use the date time functions for this.
Regards,
Arjan