Recurring Flow - Cannot Get Date Condition To Work As Intended

[tbrock_troc]

Hello.

We have created a SharePoint form to allow our team to report Store Closures they run across. In addition to the details automatically captured by the SharePoint Form, we also ask for retailer, store number and closing date. The entries are saved in a table in a shared Excel file on SharePoint.

Since we often are notified in advance of the actual store closure, we want to set up an email reminder on the future date specified in the form submission (Closure Date). As a result, it did not seem correct to trigger the flow off a new form submission and figured it would be better to have the flow check the Excel and take action when there is a date match.

I've gotten most of the flow to seemingly work but I cannot seem to get the condition using the date to function properly - I am using a test where one entry should meet the condition and one entry should fail the condition however both either meet/fail no matter what I try.

• Starts with a recurring trigger - once per day - and triggers "List Rows Present in Excel":

• I use Compose to pull the Closure Date from the Excel file and a second Compose to use FormatDateTime on the first compose output to format from Excel Online day format (integer?) into a Date format:

[The expression in the second compose is: formatDateTime(addDays('1900-01-01',add(int(outputs('Compose_-_Get_Closure_Date')),-2)),'yyyy-MM-dd')]

From there I basically want a condition which says "if Closure Date = Today, send an email."

• I use the output from the second Compose (Format Closure Date) and set the condition to "is equal to" the UTCNow function. As I've understood it, UTCNow returned in a string so I wrapped in a FormatDateTime expression to format to 'YYYY-MM-DD':

[This is the FormatDay Expression I used above: formatDateTime(utcNow(),'YYYY-MM-DD')]

[Note - I had to add both compose functions and the condition to the "Apply to Each" action in order to even be able to save it and have the flow complete]

I have been using two tests with dates of 5/21/2021 and 11/28/2021 - the flow completes successfully however one of these should pass the condition, the other should fail and both currently fail. Here is a breakdown of both examples at each stage:

• Passed Recurrence and List Rows stage

• Both examples seem to pass both Compose stages and seem to result in the correct output

• The flow completes however both examples have failed the condition even though one of them should have passed as it is today's date:

How can I get the condition to use the date from the Excel table and accurately compare it to the UTCNow function?

Thanks.

[PrasadAthalye]

• Can you double check if you are using Correct Compose in If Condition?
• Can you hardcode one of the date instead of UTCNow and see if works?

[tbrock_troc]

Thank you for the response @PrasadAthalye .

• For the If statement, I believe I have selected the output from the 2nd Compose action (called Compose - Format Closure Date seen in original post )
  • outputs('Compose_-_Format_Closure_Date')

• Using a hard date (2021-05-21) still resulted in an email for all records when there should have only been 1
  • Please note that I now have 5 records in the table from additional testing I had done:
    • 5/7/21
    • 5/21/21
    • 5/22/21
    • 5/26/21
    • 11/28/21

I suppose this indicates the issue is before the IF statement and it is an issue somewhere in one of the compose steps?

Would it have anything to do with the source date information from Excel being a serial date format (string?) and my FormatDateTime has the "int()" expression in it?

If so, would this require Compose to get Excel data (existing 1st step) -> Convert to integer (new step) -> FormatDateTime (existing 2nd step)? Would this new step be the Initialize Variable action?

[tbrock_troc]

I made a copy of my flow to mess around with and it seems I may found a working flow (at least currently using a hard-coded date in the IF statement condition )

• Recurrence & Get List Rows same as original flow
• Apply to Each:
  • Get Source Closing Date - gets date from Excel table
  • Convert Source Closing Date to Integer - int(outputs('Compose_-_Get_Source_Closing_Date'))
  • Starting Date - formatDateTime('1900-1-1','yyyy-MM-dd')
  • Calculate Closing Date - formatDateTime(addDays(outputs('Compose_-_Starting_Date'),sub(outputs('Compose_-_Convert_Source_Closing_Date_to_Integer'),2)),'yyyy-MM-dd')

• End Result from 5 entries using 2021-05-21 as hard-coded date
  • 1 email corresponding to 5/21/21
  • 4 entries did not trigger an email (none had 5/21/21 date)

Is there anything I should be concerned with if I structure the flow this way?

Thanks.