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
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!