The Need:
Format a date expression in Microsoft Flow so that it can be used in a condition, or so it can flow to an Excel table as a date (and not a set of alphanumeric characters).
The Solution
Add "formatDateTime" to your date expression, and force the date to display only as precisely as a calendar day, rather than out to the second. The post, Using Dates in Microsoft Flow Conditions (Date Expressions, Part 1), walks through the basics of adding Condition steps to Microsoft Flows to better control when an automated process triggers. You may want to read that post before continuing.
Why do you need to format your dates for Excel?
If you plan to have items from a SharePoint list copy themselves to an Excel table (perhaps as part of backing up information on a list before deleting items from a list), it is likely you will want to be able to work with the Excel table later, to gather information and analyze data. You could match dynamic content dates to the Excel table fields in a Microsoft flow, and you would get information in that Excel date column. You would even get information that would tell you what date and what time the item was created or modified--but only as text. Excel would not be able to filter and sort these columns as dates.
NOTE: This Excel limitation only affects "Created" and "Modified" dates. If you have a user input DateTime field--a column you added to a SharePoint list--that date will flow to Excel as a readable date without needing special formatting. My best guess why is that because "Created" and "Modified" are calculated for every list via SharePoint software, and follow a format that goes all the way from year to second. This is very useful to me when I am tracking items on a list, and I am glad it have it NOW THAT I HAVE FIGURED THIS OUT. (It was very frustrating and upsetting before I figured this out.) Why can't Excel interpret a date from flow that goes all the way to the second? Something something something ISO? This is an interesting gee whiz article about Excel dates, if you want to take a short side trip.
If you put a created or modified date as dynamic content directly into an Excel column via Flow...
...your Excel date will display like this:
Obviously, you can tell this item was created on June 5, 2020 (at 12:29:07 PM). But you can't reformat this as a date and Excel won't be able to read it as a date. To get that functionality in your Excel table, put a date expression in your flow instead.
Select the "Expressions" menu in the Dynamic Content box. Begin your expression with the "formatDateTime ()" option. Likely it will pop up as you start typing.
The full expression in this field is:
formatDateTime(triggerBody()?['Created'],'yyyy-MM-dd')
Note the format of the date. yyyy-MM-dd. Year-Month-Day. MM is not the same as mm, so make sure you pay attention to letter case. yyyy-mm-dd will not work.
How do you know what to put between "formatDateTime" and "'yyyy-MM-dd'" in your expression?
Flows can get pretty complicated, and you might have a condition inside a "Switch" or "Apply to each" or "Condition," or nested two or three tiers deep. The flow above is a "trigger body" Created date; sometimes you get "items('Apply_to_each_3')" or similar. Your flow won't find the right date if you don't have the expression written correctly.
I don't know all the expression possibilities well enough to just create the expression by typing; I have to copy it. Luckily, you can use the dynamic content button to help you with the expression. In the example below, we are using Microsoft Flow to put a SharePoint list "Modified" date into an Excel table column called "Modified."
1. Put the actual date dynamic content selection into the field.
2. Hover over the dynamic content selection to see how the dynamic content is coded.
3. Copy the hover text into your formatDateTime(), in the Expression field on the dynamic content box.
Add the 'yyyy-MM-dd' to the expression and click OK to save. The full expression in the example below is formatDateTime(triggerBody()?['Modified'],'yyyy-MM-dd').
Why do you need to format your dates for SharePoint conditions?
What if you aren't using Excel at all in a flow? What if you are merely comparing a SharePoint date to utcNow in a condition? What different does it make if it is formatted?
If you create a Microsoft flow with a condition that compares a date in a SharePoint list to utcNow--even if it is a user input date and not a Created or Modified date--the Condition will compare that date to utcNow. To right this year/month/day/hour/minute/second now. Exactly now. If your condition is "greater than," "greater than or equal to," "less than," "less than or equal to," or "is not equal to," you do not have to format the date. It won't matter how precise the utcNow dates for greater/lesser date comparisons.
But if you want to use a condition that asks, is [my date] equal to utcNow, you have to format both your date AND the utcNow date. If you try to compare the date to utcNow and need a YES/True result from your condition, your flow will compare the regular date to utcNow and if that date isn't exactly equal to the second of utcNow, it will return a NO/False result and your flow will stop.
Format your date and utcNow on both sides of your "is equal to" condition to force a comparison between yyyy-MM-dd and yyyy-MM-dd. You want to compare apples to apples. Below is an excerpt from a recurring (daily) flow that alerts an approver if a new item has been submitted to a SharePoint list.
The full expressions in the formulas are:
formatDateTime(items('Apply_to_each')?['Created'],'yyyy-MM-dd') is equal to formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')
In narrative form: We want to find items that were created today and send emails for each of these items. We have formatted the "Created" date, which is in a condition inside an "Apply to each" box, as yyyy-MM-dd. We want to compare it to utcNow, but it is already past midnight when this flow runs, so we have to subtract a day from utcNow before comparing the dates. Otherwise, we would be comparing today's submitted/created date to a utcNow tomorrow date.
UPDATE July 25, 2021:
Click here for a link to a "Let's POWER Automate" blog post with other date formats to use in your expressions.
The Remains of the Day by Kazuo Ishiguro. A novel in which how things are done is perceived as more important than what things are done, although not by every character. Nevertheless, if every character in that book used SharePoint, they would all agree that how you format dates in Microsoft Flow is the most important of all.
Comments
Post a Comment