Comparing Dates and Ignoring Year

Options

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.

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!