Condition comparing dates

[adr55555]

I am having an issue with using a date in conditions. My flow needs to compare an Excel date to a SharePoint list date and then update the SP list item if the condition is met. I have tried numerous approaches, but all fail to work properly. If the Excel date is more recent than the SP list date, the item should be updated. Some items are updating even though the dates match. Here is an example of matching dates.

 

I have tried writing the condition with both dates formatted as strings and utcNow as well as the Excel date as a string and utcNow and the SP list date as is. I have tried the condition as less than, greater than, equal to, not equal to. Alas, no matter what I do, items update that should not update. 

Any help would be much appreciated.

[abm · Accepted solution]

Hi @adr55555 

Try using formatDateTime() expression yyyy-MM-dd format for both Excel and SharePoint dates for comparison.

Thanks

[adr55555]

Your suggestion worked, so thank you!

Why does formatDateTime work but not string or utcNow or ticks? 

[abm]

Hi @adr55555 

UTCNow() returns with date with hour, minutes and seconds. To retrieve the date without time, minutes and seconds you need to use the formatDateTime(). 

[adr55555]

I actually was in here trying to figure out how to cancel the "accept as solution" when you responded. The solution isn't actually working beyond the small test batch that I initially ran. Despite dates matching, items are still being updated.

[abm]

Hi @adr55555

Could you please post a screenshot of your flow and a sample of your Excel data?

Thanks

[adr55555]

 I added the second EffectiveDate column today. Its full name is EffectiveDate_Use and is formatted as text. The original column is formatted as Short Date.

Before I added the second column, I would use ISO 8601 for DateTime Format. Now that I have the EffectiveDate formatted as text, I am leaving that field blank.

The next few steps I added so that I can visualize what is happening.

 Then I pull the SP List items. The Apply to each 2 action contains: 

For now I have stopped here because the condition continues to return true when it should say false.

Condition is 

formatDateTime(variables('ExcelEffectiveDate'),'yyyy-MM-dd') is greater than 

formatDateTime(variables('SPlistEffectiveDate'),'yyyy-MM-dd').

[adr55555]

I do finally have this flow working!