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

I have the same question (0)

All responses (13)

Answers (0)

Michael E. Gernaey 53,362 Super User 2025 Season 2 on at

Like (1)

Report

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?

Was this reply helpful? Yes No
CU-19080415-3 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Chriddle 8,441 Super User 2025 Season 2 on at

Like (1)

Report

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

Just check against utcNow()

Was this reply helpful? Yes No
CU-19080415-3 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Chriddle 8,441 Super User 2025 Season 2 on at

Like (1)

Report
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)))

Was this reply helpful? Yes No
CU22080237-3 10 on at

Like (1)

Report

Thanks for the correction, what about the check that the expiry must be greater than the current date ? to filter the past records out.

Was this reply helpful? Yes No
CU22080237-3 10 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Chriddle 8,441 Super User 2025 Season 2 on at

Like (2)

Report
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" } ]

Was this reply helpful? Yes No
CU22080237-3 10 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Chriddle 8,441 Super User 2025 Season 2 on at

Like (1)

Report

Your expirydate is in the year 1753? ;)

It seems that there is something wrong with your data.

Was this reply helpful? Yes No