Comparing dates using power automate

[cmspowerauto]

Hello-
I'm trying to compare dates and having issues with my flow. I have a next review date in a column in an MS List (for routine work our team performs), and then compare it to today's date + 30 so that an email will be sent and to-do's added in Outlook so that upcoming work can be tracked. I've tried just comparing the dates using format, I've tried changing it to integers, etc. Could someone assist with what is wrong with my flow. I'm sharing a few pictures of the different pieces of the flows that I've tried, but note that in this set-up that you see, really the only thing that is working right now is that it is going to the MS List, it's pulling the data, and I'm trying to compare the dates. The first few steps of the flow I just haven't deleted in case it turns out I need to use them for some reason.

My last item is returning this error below.

Column from MS List: Next Review Date

The left side of my comparison:
addDays('1900-01-01T00:00:00Z',sub(int(item()?['Next_Review_Date']),33))

The right side of my comparison:
addDays('1900-01-01T00:00:00Z',sub(int(item()?['utcNow()']),2))

Thank you-

[Pstork1]

You don't need to jump through all those hoops.  You can work with the dates directly to do the math.  But you do need to do your condition on a range to make sure it doesn't keep firing.  Add two rows to the condition and use an 'And' to connect them. Assuming your recurrence trigger  is set to daily  you can use the following.

Right Side of Greater than
addDays(UTCNow(),-1)

Right Side of Less than
UTCNow()

Left Side of Both
addDays(item()?['Next_Review_Date'],-30)

Using that condition the yes side of the condition will fire when the Next Review Date is 30 days in the future.

[ScottShearer]

@cmspowerauto 

I agree with @Pstork1 - he is right on point.  However, I believe that you need to format the output of UTCNow() to match the format in which Lists/SharePoint stores dates.

For example:

addDays(UTCNow(),-1,'yyyy-MM-dd')

Rather than UTCNow() for the right side of less than consider using:

formatDateTime(UTCNow(),'yyyy'MM-dd')

The idea that the date formats need to match for the condition to work properly.

[Pstork1]

Actually, I disagree.  YOu are correct that UTCNow will return the date time in ISO 8601 format, but since you are doing AddDays() on the SharePoint date that will also be in ISO 8601 format.  So formatting UTCNow to match SharePoint will actually break the comparison.  Here's the output of Compose statements for UTCnow and AddDays for SharePoint.   And the comparison works.

[cmspowerauto]

Thank you both for your input. I've tried to make these updates, @Pstork1  and it's giving an error of invalid template. I've attached a picture... Any ideas?

  

[Pstork1]

The error means that the date value you are feeding in is null.  Other than syntax the formulas are correct.  I would suggest adding some compose statements temporarily to check the value of 

item()?['Next_Review_Date']

[cmspowerauto]

I think I'm confused... I have an MS list of ~280 line values. Correct, some of them are blank for the 'Next Review Date'- which as you state is causing this Null error.  But if the formula is working, shouldn't it still send the email if it hits at this 30 day review with the formula?  This is where my confusion lies.  I have several dates in there that are at the 30 day reminder period for today and I'm not getting any email notification? 

[Pstork1]

The issue is that the flow will fail when it encounters the null date, probably before it ever gets to sending the first email.  Is some of the Review Dates are empty you need to check that as the first row in the condition so it will evaluate to false based on that before it tries to actually evaluate the date.

[cmspowerauto]

Thank you for the explanation- I understand now. I went to my MS List and filled in all of the 'empty' dates, and I'm still getting a 'null' error with 'no inputs'. I have no idea what I'm doing wrong with this... 

[Pstork1]

As I suggested.  Temporarily add compose statements to show you what is coming through for inputs.  That's the only way to be sure what is going wrong.  Once you figure it out you can remove them.