Skip to main content
Community site session details

Community site session details

Session Id : 1aU4+tNuzXCpBPRSSrx5CH
Power Automate - Building Flows
Answered

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

Like (0) ShareShare
ReportReport
Posted on 4 Jul 2023 04:37:33 by 32

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 

Q1 PA.png

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 ☹️

 

 

 

  • ina295 Profile Picture
    32 on 07 Jul 2023 at 03:19:53
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    nevermind i found reason why its dubplicate 😅

     

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

  • ina295 Profile Picture
    32 on 07 Jul 2023 at 02:13:38
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    @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 😥

  • Verified answer
    Ellis Karim Profile Picture
    11,155 Super User 2025 Season 2 on 05 Jul 2023 at 11:52:26
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    Hi @ina295 ,

     

    Please check the following:

    ekarim2020_0-1688556838305.png

     

    ekarim2020_1-1688557656555.png

    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 Profile Picture
    32 on 05 Jul 2023 at 05:59:26
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    is there anyone can help me ? 😫

  • ina295 Profile Picture
    32 on 05 Jul 2023 at 04:21:57
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

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

  • v-bofeng-msft Profile Picture
    on 05 Jul 2023 at 03:09:47
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    Hi  @ina295

     

    Please try

     

    item()?['ReminderDate']

     

     

     

    equals

     

    startOfDay(utcNow())
     
     
     

    Best Regards,

    Bof

  • ina295 Profile Picture
    32 on 05 Jul 2023 at 02:58:35
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    hi @v-bofeng-msft it is succesfull, 

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

    ina295_0-1688525825768.png

     

    do u know how to solve this problem? 

  • v-bofeng-msft Profile Picture
    on 05 Jul 2023 at 01:18:58
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    Hi @ina295 ,

     

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

    vbofengmsft_0-1688519923197.png

     

     

    Best Regards,

    Bof

  • ina295 Profile Picture
    32 on 04 Jul 2023 at 09:16:12
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    Still not working

     

    but I tried to change to 

    ina295_1-1688461995802.png

     

    ina295_0-1688461955970.png

     

    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 Profile Picture
    on 04 Jul 2023 at 08:59:13
    Re: have trouble with Date Time formating in filter Array for Reminder Date Data In Excel

    Hi @ina295 ,

     

    Please try:

     

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

     

    Best Regards,

    Bof

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2