Unanswered

Excel Date Range filter

Like (1) Share

Report

Posted on 19 Aug 2024 02:53:13 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:

Process Advisor

All responses (13)

Answers (0)

CU22080237-3 10 on 27 Aug 2024 at 05:17:38

Like (1)

Report

Excel Date Range filter

Hi,

I had a closer look at the raw output being given out by the list rows in table and have realized that it has only been pulling 256 rows instead of the thousands of rows.

This was fixed by editing the pagination.

The flow is now working as intended!!

Thank you so much for your help.
Chriddle 7,708 Super User 2025 Season 1 on 26 Aug 2024 at 09:06:50

Like (1)

Report

Excel Date Range filter

Can you scroll to such a record in the Filter's From and show it to me?
CU22080237-3 10 on 26 Aug 2024 at 01:40:52

Like (1)

Report

Excel Date Range filter

Hi Chriddle,

The data is pulled from sql into excel hence some data year are 1753. However, there are data that are within 90 days.
Chriddle 7,708 Super User 2025 Season 1 on 23 Aug 2024 at 09:59:20

Like (1)

Report

Excel Date Range filter

Your expirydate is in the year 1753? ;)

It seems that there is something wrong with your data.
CU22080237-3 10 on 23 Aug 2024 at 04:32:00

Like (1)

Report

Excel Date Range filter

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 7,708 Super User 2025 Season 1 on 22 Aug 2024 at 08:16:58

Like (2)

Report
Excel Date Range filter

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 22 Aug 2024 at 04:03:39

Like (1)

Report

Excel Date Range filter

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.
CU22080237-3 10 on 22 Aug 2024 at 02:39:31

Like (1)

Report

Excel Date Range filter

Thanks for the correction, what about the check that the expiry must be greater than the current date ? to filter the past records out.
Chriddle 7,708 Super User 2025 Season 1 on 20 Aug 2024 at 12:35:12

Like (1)

Report
Excel Date Range filter

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)))
CU-19080415-3 on 20 Aug 2024 at 01:09:42

Like (1)

Report

Excel Date Range filter

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