Need help with IF formula
Hello!
I want to use an RGB Symbol formula that first looks at a 'Completed' cell and if "checked" returns green, but if not checked it moves on to look at a "Date" column to return RED, YELLOW, BLUE or Upcoming. Here is my current formula:
=IF(OR(COMPLETED@row = 1, "Green")), IF(DATE@row < TODAY(), "Red", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()) + 1, "Blue", "Upcoming"))))
Best Answers
-
Not sure why you thought you needed OR in there. You just need a series of nested IFs, where each is the negative condition of the IF before it:
=IF(COMPLETED@row = 1, "Green", IF(DATE@row < TODAY(), "Red", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()) + 1, "Blue", "Upcoming"))))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Typically, a completed task is marked Blue and an upcoming or in-progress task (to be completed in future) is marked Green, so going with this logic try this,
Also please rename your DATE column to something like "Target Date" or "Finish Date" etc. as DATE is also a function name and can be confusing.
=IF(Completed@row, "Blue", IF(WEEKNUMBER([Finish Date]@row) < WEEKNUMBER(TODAY()), "Red", IF(WEEKNUMBER([Finish Date]@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER([Finish Date]@row) > WEEKNUMBER(TODAY()), "Green"))))
-
Glad it helped!
If you could mark my answer as Accepted, that would be great!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Not sure why you thought you needed OR in there. You just need a series of nested IFs, where each is the negative condition of the IF before it:
=IF(COMPLETED@row = 1, "Green", IF(DATE@row < TODAY(), "Red", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()) + 1, "Blue", "Upcoming"))))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Typically, a completed task is marked Blue and an upcoming or in-progress task (to be completed in future) is marked Green, so going with this logic try this,
Also please rename your DATE column to something like "Target Date" or "Finish Date" etc. as DATE is also a function name and can be confusing.
=IF(Completed@row, "Blue", IF(WEEKNUMBER([Finish Date]@row) < WEEKNUMBER(TODAY()), "Red", IF(WEEKNUMBER([Finish Date]@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER([Finish Date]@row) > WEEKNUMBER(TODAY()), "Green"))))
-
Jeff! That was it... thanks so much. I thought I needed the OR to create a Smartsheet 'ELSE' (I know doesn't actually exist). Thanks again!
-
Glad it helped!
If you could mark my answer as Accepted, that would be great!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
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!