Flow reminder from a SharePoint List Calculated Field Date not meeting expression checks

[Anonymous]

Hi all.

I'm trying to get flow to send an email reminder on a calendar date that is listed in a Calculated Field but I can't seem to get it to send anything or get the condition checks to meet the criteria to send.

Essentially I have a 'S1Reminder' field that is set up as a Date/Time Calculated Field and I'm trying to get Flow to use the condition to send when S1Reminder is equal to utcNow(). At the start of the flow I get utcNow converted into dd/mm/yyyy to match up the format of the calculated field, but try as it might it won't match up the conditions if I do "equals". I'm a bit stumped!  

Image 1Image 2

[Anonymous]

I'm trying to get flow to send an email reminder on a calendar date that is listed in a Calculated Field but I can't seem to get it to send anything or get the condition checks to meet the criteria to send.

Essentially I have a 'S1Reminder' field that is set up as a Date/Time Calculated Field and I'm trying to get Flow to use the condition to send when S1Reminder is equal to utcNow(). At the start of the flow I get utcNow converted into dd/mm/yyyy to match up the format of the calculated field, but try as it might it won't match up the conditions if I do "equals". I'm a bit stumped!  

Image 1Image 2

[Pstork1]

You'll need to apply a similar formatting to the S1 reminder field also.  By default Date fields, even calculated ones, include both a date and a time.  So the chance that you will meet an equal condition based on a time field measured in milliseconds is almost impossible.  You need to apply the same formatting that you do to the UTCNow to get it filtered down to a text string containing a formatted date.

[Anonymous]

Thanks for your help PStork1,

Had a feeling that was the case, I've used the code formatdatetime(items('ComplaintsData')?['S1Reminder'],'dd/MM/yyyy') to convert S1Remind into the dd/MM/yyyy format like utcNow was set up in the previous step, and I've named the Compose process 'S1RemindConvert' (and renamed the utcNow compose process to 'utcNowConvert'. 

However, S1RemindConvert doesn't appear in the Dynamic content list for the condition check to match up the two values, not quite sure what's happened, any ideas?

Step 3Step 4

[Pstork1 · Accepted solution]

Since you have multiple records to evaluating you need to do the conversion at the point where you are applying the condition, not in a loop earlier in the Flow.  REplace the s1CompletionDate dynamic content in your condition in Step #3 with the formula you used for your compose.

[Anonymous]

That did the trick! Thank you so much! 

For those stuck on this themselves I'll attach the end result of what I put in and got it to work for the more visual types. Step 1Step 2

[h1k4ck]

Hi, 

I have the same situation, created the same flow, but the condition seems to be always false, even it should be true!

Any Ideas?

2 formulas:

formatDateTime(items('reminder')?['Remind_x0020_date'],'MM/dd/yyyy')

formatDateTime(utcnow(),'MM/dd/yyyy')

[Pstork1]

Take a look at one of your recent runs to make sure that the dates actually match.  I suspect your issue is that you are comparing a date from UTC to a local timezone as stored in SharePoint.  Since those can be hours off it can actually throw the date off.

[h1k4ck]

When i check only data is shown, and even when data is the same result is false.

How to put all in local?
any other thougt?

Tnx

[Anonymous]

Have you tried adding a Convert Timezone step?

https://flow.microsoft.com/en-us/blog/working-with-dates-and-times/ 

[Pstork1]

Can you provide a screenshot of some of the data being used in the comparison?

[h1k4ck]

adding conversion it works!

posting the flow for other user:

[Pstork1]

Problems working with dates are almost always an issue with the time component or a timezone mismatch.