Hello, I want to build a flow to notify users based on a field status in my SharePoint list, I know how to build this part. However I want to fil

Solved: Wrong Date Format in SharePoint List - Convert it and Filter to Right Date Format inside Automated Flow.

Wrong Date Format in SharePoint List - Convert it and Filter to Right Date Format inside Automated Flow.

Ok I have a way to get the date into the format and then filter the items.

Beginning Example of Expression to Format Date Time:

But the expressions will start getting long, so I want to show you the first part which is just getting the date into the correct format. This is not what the final flow will look like, but it is an example of what I'm going to be using.

I have made a SharePoint column and named it "ExecutionDate" and filled in some text similar to yours.

Example SharePoint Column

So lets say all I wanted to do was to get the date formatted for each item. I would use the Get Items to retrieve all of the rows from the SharePoint List. Then I would add a Compose to the flow where I can make the expression.

Example for Formatting the Date Time

In my example I'm using the expression:

parseDateTime(trim(first(split(item()?['ExecutionDate'],'-'))),'en-us','M/d/yy')

Working inwards to out this expression says:

split() - split the value where there is a '-' dash in the text string
first() - get the first part of the string that was split
trim() - remove any spaces from the text string
parseDateTime() - use the format 'M/d/yy' to read my value and convert it into datetime format

You can reference these functions here on Microsoft Learn Reference Guide

Here are a few of the item results with the datetime formatted.

Results of DateTime Conversion

Can you make a similar example? Or do you understand what is happening to the string?

Basic Filter Query Example:

To filter the Get Items, you will have to use the "Filter Array" action. This will use the values from the Get Items and filter it by the conditions that are set. (After the filter array the rest of the data in the flow needs to reference the Filter Array as the source, not the Get Items.)

So lets say we want to filter the Get Items to have only items with an Execution Date that is greater than or equals 3 days from today. We will use the previous long expression of parseDateTime() to format the value, and compare it to the utcNow() function that gets the current time. If we include the addDays() function and add -3 days to utcNow() we will get 3 days before the current time.

Use the main ParseDateTime expression:

parseDateTime(trim(first(split(item()?['ExecutionDate'],'-'))),'en-us','M/d/yy')

is greater than or equal

Use the -3 days added to current time:

addDays(utcNow(),-3)

The Filter Array will look like the example below -

Basic Filter Array Example

But this isn't exactly accurate due to how UTC timezone and our formatted datetime are compared. Our formatted parseDateTime is midnight in our timezone. utcNow() is the current time of day. So by subtracting 3 days from the current time and comparing to our parseDateTime, the our values that are midnight 3 days ago would actually be not included since they are less than current time.

So to correct this, we need to convertTimeZone and set the time of day to midnight, and then subtract 3 days.

This is very similar to the previous Filter Array except the right side expression has been edited.

Use the main ParseDateTime expression:

parseDateTime(trim(first(split(item()?['ExecutionDate'],'-'))),'en-us','M/d/yy')

is greater than or equal

Use the -3 days added to the converted timezone:

addDays(convertFromUtc(utcNow(),'Eastern Standard Time','yyyy-MM-dd'),-3)

The corrected Filter Array will look like the example below -

Updated Basic Filter Array - Execution Date is greater or equal than 3 days ago

So this is a basic Filter Array that will return items that are from -3 days ago using our formatted parseDateTime() value and the converted timezone value.

Advanced Mode - Edit Filter Array:

Since the filter array only allows 1 condition (the three boxes) in the basic mode, we need to switch it to advanced mode to get the complete filter.

Advanced Mode - Edit Filter Array conditions

Click the Edit in Advanced Mode in the Filter Array and it will switch the boxes into the full expression code. So our 3 little boxes is actually the full expression:

@greaterOrEquals(parseDateTime(trim(first(split(item()?['ExecutionDate'], '-'))), 'en-us', 'M/d/yy'), addDays(convertFromUtc(utcNow(), 'Eastern Standard Time', 'yyyy-MM-dd'), -3))

Second Condition:

The reason we need a second condition, is because the single condition above will get every date that is above 3 days ago. So if there are dates that are next week or next year, they would be returned in the filter array.

What we want to have is Get Executions Dates

Greater Than or Equals 3 Days Ago AND Less Than or Equals Today

Less Than or Equals Today

So to make the second expression that compares Execution Dates that are less Than or Equals Today. It is basically the first expression we used, except it does not need to subtract 3 days. And instead of greater than or equals we will use less than or equals. And since we need to be comparing to full dates instead of midnight time, I will edit the parseDateTime so that it matches the converted timezone.

If we made a filter array that just had the condition of get Execution Dates that are less Than or Equals Today it would look like the Filter Array below. And If we turn on advanced mode it will show what the expression will be.

Example Less Than or Equals

The Expression that is used is:

@lessOrEquals(formatDateTime(parseDateTime(trim(first(split(item()?['ExecutionDate'], '-'))), 'en-us', 'M/d/yy'), 'yyyy-MM-dd'), convertFromUtc(utcNow(), 'Eastern Standard Time', 'yyyy-MM-dd'))

Remove the '@' symbol at the beginning and this is the other condition we will use.

Completed Filter Array Condition:

So using all of the above we can make an advanced expression for the Filter Array that will get all Execution Dates that are Greater Than or Equals 3 days before today AND Less Than or Equals Today.

We basically combine the two large expressions with the and() function.

Greater Than or Equals 3 days before today:

greaterOrEquals(parseDateTime(trim(first(split(item()?['ExecutionDate'], '-'))), 'en-us', 'M/d/yy'), addDays(convertFromUtc(utcNow(), 'Eastern Standard Time', 'yyyy-MM-dd'), -3))

Less Than or Equals Today:

lessOrEquals(formatDateTime(parseDateTime(trim(first(split(item()?['ExecutionDate'], '-'))), 'en-us', 'M/d/yy'), 'yyyy-MM-dd'), convertFromUtc(utcNow(), 'Eastern Standard Time', 'yyyy-MM-dd'))

Full Combined Expression using the and() function:

@And(greaterOrEquals(parseDateTime(trim(first(split(item()?['ExecutionDate'], '-'))), 'en-us', 'M/d/yy'), addDays(convertFromUtc(utcNow(), 'Eastern Standard Time', 'yyyy-MM-dd'), -3)),lessOrEquals(formatDateTime(parseDateTime(trim(first(split(item()?['ExecutionDate'], '-'))), 'en-us', 'M/d/yy'), 'yyyy-MM-dd'), convertFromUtc(utcNow(), 'Eastern Standard Time', 'yyyy-MM-dd')))

It will look like this Filter Array below even though you can't see the full expression in advanced mode -

Full Expression in Filter Array - (used photo edit to show full text box)

So I know I wrote a lot for this example, but I wanted to be sure that you understood how this built up into the large expression instead of just throwing something on here.

Let me know if this works for you,

Quick Links

Leaderboard > Power Automate