Unanswered

Condition comparing dates

(1) Share

Report

Posted on by 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.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

abm abm 32,865 Most Valuable Professional on at

Like (1)

Report

Hi @adr55555

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

Thanks

Was this reply helpful? Yes No
adr55555 54 on at

Like (0)

Report

Your suggestion worked, so thank you!

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

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

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().

Was this reply helpful? Yes No
adr55555 54 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @adr55555

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

Thanks

Was this reply helpful? Yes No
adr55555 54 on at

Like (0)

Report

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').

Was this reply helpful? Yes No
adr55555 54 on at

Like (1)

Report

I do finally have this flow working!

Was this reply helpful? Yes No