Unanswered

Excel Date Range filter

(1) Share

Report

Posted on by CU-19080415-3

Hi all,

I am trying to create a flow that takes items with warranty expiring in the coming 90 days from today for my admin team to follow up on.

Currently i am struggling to get the filter right as I'm using a excel list instead of sharepoint lists, the filter query doesnt accept greater or lesser.

I have worked around this by creating two filter arrays with the exact greater than and less than but it doesnt seem to be querying the right dates

please refer to below. any help would be greatly appreciated

best regards,

Anatam

Categories:

General topics

All responses (13)

Answers (0)

Sort by

Michael E. Gernaey 53,970 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi,

I see you listing Filter Array 2 before filter array

1. Please share your flow as a whole
2. Please share the Input of your Filter Array

Also,

Based on your FilterArray2, you are comparing the entire Value, to a Date

What is the Date Column you are supposed to be pointing at in Filter 2?
CU-19080415-3 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi,

the list array arrangement has been rectified.

1. this is the whole current flow

(still missing the condition if return empty do not compose email)

2. here is a small sample on what is return in the input from the excel workbook

{

    "@odata.etag": "",

    "ItemInternalId": "b5663bf7-525b-4e4d-a472-d45c682b2132",

    "Item No_": "23",

    "Variant Code": "WOOD",

    "Name": "testing Co",

    "Description": "2x1 wood clock",

    "Quantity": "1",

    "Serial No_": "04323112",

    "expirydate": "2021-02-09T00:00:00.000Z"

  }

i am trying to point towards the expirydate field filtered out by array 1.

I understand that as this is from an excelbook i am unable to use the filter query directly as its not supported.

Thanks.
Chriddle 8,729 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Remove the formatDatTime() function from the Filter. Your Excel output's date is not formatted like this ;)

Just check against utcNow()
CU-19080415-3 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Chriddle,

But i need the adddays check for 90days in advanced.

How should i go about it if i check against utcNow() only?

Should array 1 be the check for greater than utcNow()
and array 2 be expiry lesser than addDays(utcNow(), +90) ?

or should i swap it around. Cant tell if im doing it correctly as it doesnt return the correct dates.

Thanks
Chriddle 8,729 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
All I was saying is that your date format is wrong and you need to remove (or adjust) the formatDateTime() function to make it work.

One way to check the 90 days rule, use this:

@lessOrEquals(startOfDay(item()?['expirydate']), startOfDay(addDays(utcNow(), 90)))
CU22080237-3 10 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks for the correction, what about the check that the expiry must be greater than the current date ? to filter the past records out.
CU22080237-3 10 on at

Like
a
(1)

Report
Copy link

Link copied!

Would be a great help if there is a way to just get only items that has an expirydate within the range of 90 days from today.
Chriddle 8,729 Super User 2026 Season 2 on at

Like
a
(2)

Report
Copy link

Link copied!
I added the format string 's' to avoid issues with the time zone component ('Z') in utcNow()

@and( greaterOrEquals( startOfDay(item()?['expirydate'], 's'), startOfDay(utcNow(), 's') ), lessOrEquals( startOfDay(item()?['expirydate'], 's'), startOfDay(addDays(utcNow(), 90), 's') ) )

Result

[ { "Name": "test0", "expirydate": "2024-08-22" }, { "Name": "test0", "expirydate": "2024-08-30" }, { "Name": "test0", "expirydate": "2024-09-01" }, { "Name": "test0", "expirydate": "2024-09-02" }, { "Name": "test0", "expirydate": "2024-09-03" } ]
CU22080237-3 10 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Chriddle thanks for the array

However, seems like it is not capturing anything. could the date format be the issue ? I have checked the raw input and there are dates that fall between the 90 days. Appreciate your kind help.
Chriddle 8,729 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Your expirydate is in the year 1753? ;)

It seems that there is something wrong with your data.