Filter array based on dates, with expression

(4) Share

Report

Posted on by ND3

Hello

I have a list of dates and names generated from the get items action in excel online.

The dates are in ISO8601 format.

This is an example of an item in the array:

I want to retrieve everything of the next month and i use this formula in the filter array action:

@equals(formatDateTime(item()['Datum'], 'MM'), addDays(utcNow(), 15, 'MM'))

(I add 15 days to get to the next month as this flow runs always on the 25th).

I get this error:

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(formatDateTime(item()['Datum'], 'MM'), addDays(utcNow(), 15, 'MM'))' failed: 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

I presume there is a problem with the item()['Datum'] in the formula?

Thanks for any help

Neil

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (1)

Suggested answer

trice602 15,404 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

Yes the issue is with formatDateTime(). Modify both to this format, 'yyyy-MM-dd'. The formula will still be valid, so if the Datum value is 2024-09-22 and the addDays expression +15 equals 2024-09-22 this works. This essentially does exactly what you want to do with the updated format. Please edit, save, and retest.

------------------------------------------------

⭐ If this was helpful, please like and/or mark as a verified answer to help others find this too!

Always glad to help! 💯💯💯💯💯

Tom

Follow me on LinkedIn - Thomas Rice, PMP | LinkedIn

Was this reply helpful? Yes No
Verified answer

ND3 34 on at

Like (0)

Report

Hello

thank you for your suggestion, unfortunately it still gives the same error.

For some reason it does not want to handle formula manipulating a date entry as a string in an array.

I have fixed it by changing the formula:

this works for me:

@and

(
less(item()['Datum'], startOfMonth(addDays(utcNow(), 45))),
greater(item()['Datum'], startOfMonth(addDays(utcNow(), 15)))
)

So i do not manipulate the item()['Datum']. Very glad to see the bolean operator work correctly on dates.

This gives me all the entry's for the next month.

Greets!

Was this reply helpful? Yes No