Using Dates in Microsoft Flow Conditions (Date Expressions, Part 1)

Posted by on
Needs:
  1. Preventing duplicate or untimely actions within Microsoft flows that start with the "When an item is created or modified" trigger.
  2. Creating more sophisticated automations that use date/times beyond the "Created" and "Modified" dynamic content options.

The Solution:
Learn how to write date "expressions" for your Microsoft Flow condition steps.

1. Limit how often a flow triggers actions.
Below is a very basic flow. Whenever an item is created or modified on a SharePoint list, an email will go out. This could end up being a very large number of emails, depending on how you are using the list.



Perhaps you only want an email to go out when there is a change to an item on the list. So, you add a condition to your Microsoft flow:



When an item is created or modified, the flow will compare the "created" date to the "modified" date. "Modified" date is the last time an item was touched. At the moment of creation, the modified date is the same as the created date. So, if "created" date is equal to "modified" date (YES), then no further steps are taken. The flow stops. But if "created" date is not equal to "modified" date (NO), then this means a list item has been changed. An email will go out.

Some possible uses for an automation like this could be:
  • A task on a list of support requests has been marked complete; an email goes out to the requestor of the support.
  • A person has been assigned to handle an item on the list; an email goes out to that person alerting them that they have been assigned.
  • A piece of equipment has been loaned to an employee; an email goes out to the person in charge of the inventory, so the equipment can be prepared for delivery.
  • A product feature has been updated; an email goes out to the team that manages product promotional materials.

2. Create a date expression to capture more dates than just "created" and "modified."
Look closely at the window that pops up when you were creating the condition. You have a "Dynamic content" menu and an "Expression" menu.



The image below shows the utcNow date, which is "today in the UTC time zone."


To use the Expression menu, enter your expression and click "OK." The expression will appear in the dynamic content field, as a pink function. To update an expression in a flow, click on the pink function, make your change in the Expression field, and then click "Update."


Some useful date expressions:

  • utcNow() = Today, in the UTC time zone
  • addDays(utcNow(),-1) = one day ago, in the UTC time zone (you have added negative one days)
  • addHours(utcNow(),-8) = eight hours behind UTC time zone, which is Pacific Standard Time. If you want a date to be recorded as "today" in the Pacific Time Zone no matter what time it is in UTC, you have to subtract 8 hours from UTC. Otherwise, you might get a date a calendar day ahead if it is already the next day in UTC when your automation runs.
A sample Microsoft flow with a date range expression
The flow below acts weekly on a list that contains items that have due dates. Every Tuesday, the flow checks the list for due dates that passed during the previous week, puts those items into an Excel table, and then sends an email message with the Excel table attached to people who need to know that dates have passed. July 6 is a Tuesday, so the report will contain items with dates from Saturday, June 26 through Friday, July 2. (The email step is not shown here.)

The Control step contains the following two conditions:
Formatted DueDate "is greater than" formatted eleven days ago
AND
Formatted DueDate "is less than or equal to" formatted four days ago

That is to say...
formatDateTime(items('Apply_to_each_2')?['DUEDATE'],'yyyy-MM-dd') is greater than formatDateTime(addDays(utcNow(),-11),'yyyy-MM-dd')
AND
formatDateTime(items('Apply_to_each_2')?['DUEDATE'],'yyyy-MM-dd') is less than or equal to formatDateTime(addDays(utcNow(),-4),'yyyy-MM-dd')

I sneaked in some formatting there without warning. Why do we have to format the due date and the utcNow date before we can compare them? That question will be answered in my next post on July 12, "Formatting Microsoft Flow Date Expressions for SharePoint and Excel (Date Expressions, Part 2)." Once you know how to format date expressions, you can use Microsoft Flow for all kinds of automations.

A note on a text:
The Nix by Nathan Hill. In which Samuel the Author is propelled, under pressure, to adventure to avoid either finishing his book or repaying his advance. Things might not have snowballed if his editor had mastered the art of the automatic due date reminder.

Comments

Post a Comment