cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adr55555
Advocate I
Advocate I

Condition comparing dates

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.

 

 ExcelDate.pngSPdate.png

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
abm
Super User
Super User

Hi @adr55555 

 

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

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

View solution in original post

7 REPLIES 7
abm
Super User
Super User

Hi @adr55555 

 

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

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Your suggestion worked, so thank you!

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

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

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.

Hi @adr55555

 

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

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

ExcelData.png

 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.

 

Flow_Part1.png

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.

Flow_Part2.png

 

Flow_Part3.png

Flow_Part4.png

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

Flow_Part5.png

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

I do finally have this flow working!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,228)