Comparing Dates and Ignoring Year

Joshua Chesner

I'm trying to compare two dates: TODAY() and RENEWALDATE1. RenewalDate1 just has a month and day (9/10). Is there a way I can do IF(TODAY()<RENEWALDATE1) with ignoring the year entirely? I just want to know if today is before 9/10 or after 9/10 regardless of the year.

Thank you in advance.

David Joyeuse

Hi @Joshua Chesner

That's kinda tricky what you are asking for, it seems easy at first sight, but it requires a bit of modifications to dobefore comparing the two cells.

We could go with some helper columns, but we'd go straight for it.

Your [RenewalDate] column is text to smartsheet, and to convert this to a date we'll have to split this so the DATE function can work.

DATE like this: DATE(YEAR, MONTH, DAY)

• YEAR is easy, as we'll go with a YEAR(TODAY()) argument to fill it.
• MONTH is your left part of the [RenewalDate] column (assuming you're US here, otherwise, you'll have to use the other formula below. Since I'm not sure you'll type 03 or just 3 for month, we'll go with a FIND function to determine how many characters are on the LEFT. Which gives us:
  • LEFT([RenewalDate]@row, FIND("/", [RenewalDate]@row)-1).
• DAY is the right part of the [RenewalDate] column, here again we go with a FIND function for the "/". But since, FIND starts from the LEFt, we'll need the LEN function as well to return the characters from the right.
  • RIGHT([RenewalDate]@row, LEN([RenewalDate]@row)-FIND("/", [RenewalDate]@row))

OK, we're nearly done here as the LEFt and RIGHT returns text/string, so we need the VALUE function to convert this to numbers, so DATE can use them.

=DATE(YEAR(TODAY()), VALUE(LEFT([RenewalDate]@row, FIND("/", [RenewalDate]@row)-1)), VALUE(RIGHT([RenewalDate]@row, LEN([RenewalDate]@row)-FIND("/", [RenewalDate]@row))))

Now you do have a date that you can compare to TODAY()

=IF(DATE(YEAR(TODAY()), VALUE(LEFT([RenewalDate]@row, FIND("/", [RenewalDate]@row)-1)), VALUE(RIGHT([RenewalDate]@row, LEN([RenewalDate]@row)-FIND("/", [RenewalDate]@row))))>TODAY(),1,0)

If you're using Euro type Date display in [RenewalDate], just switch the MONTH & DAY formulas :)

Hope it helped!