Photo by Charisse Kenion on Unsplash

Flow: Testing Various Date Formats From Any Source

20th February 201923rd February 2021 AlanPs1 0 Comments convertFromUtc(), convertTimeZone(), convertToUtc(), Flow, formatDateTime(), Standard Date and Time Format Strings, utcNow()

As a Flow community contributor, I regularly assist Flow users with the creation of their Flows. The great thing about a community like the one you can find at this link is that when I need assistance with an expression, an Odata filter or the use of a connector, help is never too far way. You just can't beat support like that when working with technology.

One of the common queries that comes up relates to the use of dates in a Flow. Dates and times are an essential part of application building, business processes and day to day life so we may have to say something like the following.

If due date is next 7 days do X, If not do Y.

If current date is 2 days before user 1's birthday (taken from a SharePoint List perhaps) send me a reminder so I can get them a present.

Get all tasks for the next 14 days from a SharePoint List and send a Monday email with these tasks.

The above examples, are merely just a snippet of some of what comes up relating to the format of the date or the expression required to match a date requirement.

But what methods are suitable to test these scenarios if it's just not working is not working for you?

Would you like to buy Alan a coffee?

Visit the AlanPs1 Ko-fi page

Let's consider the following template which cane be a helpful starting point.

Here is what the conditions within the "Send a Happy Birthday email to employees on their birthday" template is doing.

"Automatically send Happy Birthday emails to employees on their birthday. Each employee's birthday is stored in a SharePoint list. The SharePoint list must contain the columns: EmployeeName, BirthDate, Email". The below (created by another) looks to have a subtle change where the column was actually called "Birthday Date" inline with formatDateTime(items('Apply_to_each')?['Birthday_x0020_Date'], 'MM-dd') in the next image. This is out the box but you get the picture.

To test these scenarios, if I were to do it like for like, then a SharePoint List would be required. This may be feasible in this example, but when a user has a lengthy Flow, 20 to 50 actions and you have been provided only a few screen shots, simulation of the issue can be required and coming up is my recommended steps of carrying out that simulation. You can trigger these simulations easily with a simple button, or a recurrence and all you do is press the play button to start the Flow running. Then once you have completed your first run, you change the expressions and use "Test Flow" with option "Using data from previous runs", as you will see below.

Firstly, let's address the brass tacks and focus in on utcNow() which links to ISO 8601, the universal standardised date format. Some explanation and background can be found here but there is lot's on the web when searching "ISO 8601" and a wiki page too.

Consider the below.

Here is it's output.

You will notice the real value of utcNow() is 2019-02-20T09:06:54.9428150Z in this example.

Let's look at the right hand side of the condition above. On the right you will see formatDateTime(utcNow(), 'MM-dd') used and now I will break that down and further demonstrate again using a "Compose" action within Flow.

With any date or time related issue, set a "Compose" action to match the input in the correct format. The same one that is coming from the SharePoint List, excel spreadsheet or a diferent source perhaps. Assuming today is my birthday (20th February 2019 at the time of writing) I will uses 02-20 as you will see above.

This means that when we consider the expression in the original template – items('Apply_to_each')?['Birthday_x0020_Date'], if it were pulling down a date of 02-20 from a list, this simulation will be like for like as both output 02-20.

In our simulation, that means that the condition looks like the below.

Let's run it by using "Test Flow" with option "Using data from previous runs". Here is the result.

Hooray, it returns true, so consider this also.

Also returns true, '02-20' = '02-20' in both examples right?

Now we will simulate a different time format for the input, this time we will go with the below.

Returns true.

Whereas the below (pay special detail to the differences outlined in format MM-dd v MM-dd-yyyy) returns false.

This is just the beginning really depending on your international needs. You may be based in the UK, the US, India or wherever really and just your local format is relevant to you but you may have to make a foray into the world of formatDateTime() in order to convert what you are working with, or use the "Convert time zone" connector highlighted below.