Flow Condition based on Excel date is not working

[SujithChand]

Team,

I am creating flow to send an email when the password is about to expire. I have an excel with below values and but the condition is not working as expected. Please see the details below :

Excel with data : 

Flow Condition : 

Condition Left side : formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['PasswordExpiry']),-2)),'yyyy-MM-dd')

Condition Left side : formatDateTime(utcNow(),'yyyy-MM-dd')

I am getting the values correctly from the expression when I am trying to project the values in the email. But the condition is returning as false.

Any input is highly appreciated! 

Thank You..

[ScottShearer]

@SujithChand 

The issue that you are experiencing is due to the fact that Excel stores dates differently than Flow Expects to see them.

A quick search on this topic will yield a ton of examples on how to handle this issue.

Here is a link to one solution.

[SujithChand]

Thanks Scott for your response! 

I have already applied the conversion (the expression used to covert was given in my original post) and I am getting expected value in the email text. But the issue I am facing is when I am using the same expression, the condition is not working as expected. 

@ScottShearer Any thoughts! 

[ScottShearer]

@SujithChand 

Sorry - I missed the conversion in your original post. 

Your screen shot indicates that you have a list rows action and then a condition.  There should be a loop after the get rows to loop through each row that is returned - is it there and you just aren't showing it in your screen shot?  The condition should be inside of the loop.

[SujithChand]

I have updated the flow as you suggested (Since I am new this platform, I missed that step, thanks for that.. :))

But still the condition returned as false. Any other thoughts @ScottShearer 

Please point out if the flow is configured in a wrong way!

[ScottShearer]

@SujithChand 

The best way to trouble shoot something like this is to look at the output of a run.

Try this - add two compose actions immediately before your condition.  Enter the expression from the left side of your expression into one of the Compose actions and the expression from the right side into the other.  Run your Flow again or test with a previous run.  Take a look at the output of each Compose action (post a screen shot here).  Does it look like your expressions did what you wanted them to?

[SujithChand]

@ScottShearer  I cannot use the same expression in compose action. getting below error as in the screenshot! Please advise!

'The template language expression 'addDays('1899-12-30',int(item()['PasswordExpiry']),'yyyy-MM-dd')' cannot be evaluated because property 'PasswordExpiry' cannot be selected.

[ScottShearer]

@SujithChand 

Please move the Compose actions inside of your loop immediately before your condition.

[SujithChand]

@ScottShearer , Please find the output below. Both compose actions returns same value but the condition is false. Ideally one row should match with the condition out of the two excel rows.

Please advise on what to do next.

[ScottShearer · Accepted solution]

@SujithChand 

It looks like there is one Compose action inside of the loop.  Please place both compose actions - one for each side of your condition - inside of the loop.  Run again and post screen shot of results.

[v-bacao-msft · Accepted solution]

Hi @SujithChand ,

If you want to compare the date in the table with the current date, you need to do this operation in Apply to each.

As @ScottShearer  suggested, you should add two Compose in Apply to each to check the value of the current date and PasswordExpiry.

Image reference:

Please take a try again.

Bets Regards,

[SujithChand]

Thank you @ScottShearer and @v-bacao-msft  for your time in helping me. Keeping both the compose function inside the loop and using the outputs in condition did the trick and everything seems to be working as expected. 

Thank you!!!