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
-
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
-
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)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!