have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

[ina295]

Hi Guys, 

Need help very badly 😥

I have flow to create Auto reminder from Excel to Email using power automate 

i made flow like this 

Eror Message is : 

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

Is there anybody here who know this issue about how to Solve it ? 
im so frustated about this ☹️

[v-bofeng-msft]

Hi @ina295 ,

Please try:

if(empty(item()?['ReminderDate']), '', formatDateTime(item()?['ReminderDate'], 'yyyy-MM-dd'))

Best Regards,

Bof

[ina295]

hi @v-bofeng-msft 

thank you for ur respons, 

i tried ur suggestion above it comes like this 

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(if(empty(item()?['ReminderDate']), '', formatDateTime(item()?['ReminderDate'], 'yyyy-MM-dd')), formatDateTime(utcNow(), 'yyyy-MM-dd'))' failed: 'In function 'formatDateTime', the value provided for date time string '45111' was not valid. The datetime string must match ISO 8601 format.'.

what do i do ? 😫

[v-bofeng-msft]

Hi @ina295 ,

Select Iso8601 here.

Best Regards,

Bof

[ina295]

i did, but still eror, and same eror.. 

[v-bofeng-msft]

Hi @ina295 ,

Please try:

if(
empty(item()?['ReminderDate']),
'',
formatDateTime(addDays('12/30/1899',int(item()?['ReminderDate']),'yyyy-MM-dd'))
)

Best Regards,

Bof

[ina295]

Still not working

but I tried to change to 

it was succesfull, but all the empty data also send to my email, so if i have 150 Data with 100 Empty Date and 10 data with Todays reminder date, all 150 data sent to my email, which is i only need is 10 data send to my email,

how to prevent the empty data and non todays date comes to email ?

[v-bofeng-msft]

Hi @ina295 ，

The Apply to each's body should be the output of 'Filter array'.

Best Regards,

Bof

[ina295]

hi @v-bofeng-msft it is succesfull, 

but no email comes in at all, i checked the filter array output is Empty 

do u know how to solve this problem? 

[v-bofeng-msft]

Hi  @ina295, 

Please try

item()?['ReminderDate']

equals

startOfDay(utcNow())

Best Regards,

Bof

[ina295]

Hi @v-bofeng-msft sadly still the same.. no value at all in Array.. 😞 

[ina295]

is there anyone can help me ? 😫

[ekarim2020 · Accepted solution]

Hi @ina295 ,

Please check the following:

the expression for filter array:

@and(not(empty(item()?['ReminderDate'])), equals(formatDateTime(item()?['ReminderDate'], 'yyyy-MM-dd'), utcNow('yyyy-MM-dd')))

not(empty(item()?['ReminderDate'])) checks that the 'ReminderDate' field is not empty or null. This is important to prevent errors when trying to use the formatDateTime function on an empty field.

Please check the output of the filter array.

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

[ina295]

@ekarim2020 hi thank you so much its working well now my filter array isnt empty anymore ! thanks again

but.. still have problem, when i have email notification, i have so many duplicates email, how to fix it ya ? do u know how ? pls 😥

[ina295]

nevermind i found reason why its dubplicate 😅

its solved thank you so much @ekarim2020 @v-bofeng-msft you guys are cool !