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

[muhepd1]

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 filter the amount of rows I check in the list, I want to filter by date as I have a field with a date but it is not formatted exactly as Date Time as it has more text. See screenshot below:

Is there a way the flow I am going to build converts the text in the Execution Date field to a Date Format (I cant change the format of the column), and then create the filter (to check only last 3 days) in the flow itself?

Could you show me how to do it please?

Thanks.

[wskinnermctc]

What TimeZone are you located?

Will any of these fields be ever be blank? Or will they always have values?

[muhepd1]

Eastern US Time (GMT - 4).

[muhepd1]

They will always have values.

Like the screenshot above, the string will show 9/23/23 - Day Time, meaning M or MM, DD, YY format.

[wskinnermctc · Accepted solution]

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,

[muhepd1]

Hi, thank you for this, I understand what you are doing, however I have an issue with the Expression.

When I am writing the expression, the 'ExecutionDate' you wrote, is it coming from the Dynamic content? Because when I try to find it, it is not there, I only get body and value from the Get items. Or are you bring that Dynamic content (ExecutionDate) from the first Compose?

As soon as I write parseDateTime the Dynamic content from the Get Items disappear:

[muhepd1]

I managed to make it work.

Now I need to tell my flow to only look for records from the 3 most recent days on the list

[wskinnermctc]

In regards to the Dynamic Content not appearing, that is annoying, but happens all of the time.

I am not using the outputs from the compose. However, I did use the compose to see what I needed to manually type into the expression. If you put the dynamic content into a compose by itself and then hover your mouse over it, then it will show you what is being used. Use that text within the expression.

I don't like how PA tries to be helpful and limit dynamic content options, but that is why it disappeared. The content is still available, if you manually type it in.

Basically you have to manually type it in.

[muhepd1]

Hi, I am going to mark your post as the solution, I made the filter array and it works. Thank you very much for that!

However, I hope you can continue helping me with something else from the same list.

Now I need to create a condition based on another column, if True I am going to send an email, when I add the condition in theory I should be bring the value of the other column from the filter array, do you know how can I do this please?

Thanks again!

[wskinnermctc]

So 2 things about continue working on this.

1. Depending on the Condition, if it is easy enough, you could just add it to the Filter Array expression.
2. I think you should make a separate post because I'm not going to be able to work on since I am about to leave. Make the post something like (Need Help Get Value From Filter Array and Check Condition), and show that you have a get items and Filter Array and then explain what you are trying to do.

If you want to look it up I think the best thing you could do would be to use a Select action from the Filter Array, and then use a Parse JSON action after the Select. (Now the Parse JSON would be the source, not the Filter Array.) 

Then use an Apply to Each on the body of the Parse JSON and you will be able to see the dynamic content options from the Parse JSON.

It would look something like this:

Filter Array - Select - Parse JSON - Apply to Each

So you can get something that looks similar to this and either look up info about these steps, or make a separate post. But this should get you in the direction you need to go.