Use of IF and AND with Dates

Hello Smartsheet Community, I would appreciate your help to get this formula working. I am trying to fill the Evaluation Status column with Current or Due based on the following criteria. if today's date is after the anniversary date AND the Date Completed is empty, complete with DUE; otherwise, CURRENT.

=IF(AND(TODAY() > [ANNIVERSARY DATE]@row,isblank([DATE COMPLETED]@row]), "DUE", "CURRENT")

Thank you in advance for your help!


Stephany

Best Answer

  • Dan W
    Dan W ✭✭✭✭✭
    edited 05/16/22 Answer ✓

    You should be able to paste this in and it will work. You added a @row to TODAY()


    =IF(AND([ANNIVERSARY DATE]@row < TODAY(), [Date Completed]@row = ""), "DUE", "CURRENT")

    I also changed the >to <.

    To be clear if the Anniversary date is lets say 5/18 you would want it to say current.

    But if the date was 5/15 you would want due?

Answers

  • Dan W
    Dan W ✭✭✭✭✭
    edited 05/16/22

    You are close. The IF statement would be If date equals, Not If Today equals. Also you can use "" to signify blank.

    =IF(AND(AnnDate@row < TODAY(), [Date Completed]@row = ""), "DUE", "CURRENT")

  • Hello Dan,

    Thank you! I revised it with your notes and still shows the #UNPARSEABLE message


    =IF(AND([ANNIVERSARY DATE]@row>TODAY()@ROW,[DATE COMPLETED]@ROW=""),"DUE","CURRENT)

  • Dan W
    Dan W ✭✭✭✭✭
    edited 05/16/22 Answer ✓

    You should be able to paste this in and it will work. You added a @row to TODAY()


    =IF(AND([ANNIVERSARY DATE]@row < TODAY(), [Date Completed]@row = ""), "DUE", "CURRENT")

    I also changed the >to <.

    To be clear if the Anniversary date is lets say 5/18 you would want it to say current.

    But if the date was 5/15 you would want due?

  • It worked! Thank you Dan 😀 I appreciate your help 👏

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!