# Use of IF and AND with Dates

Options

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")

Stephany

• ✭✭✭✭✭
Options

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?

• ✭✭✭✭✭
edited 05/16/22
Options

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")

• Options

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)

• ✭✭✭✭✭
Options

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?

• Options

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!