IF and dates functions
If Cell B is a date in the future, I need Cell A to be blank. If Cell B has a date within 30 days, I need Cell A to say "CHECK". If Cell B has a date in the past, I need Cell A to say "Expired".
However,
If Cell C is checked, I need Cell A to say "No Need". If its not checked, Cell A is blank.
However,
If Cell D is checked, I need Cell A to say "Requested". If its not checked, I need Cell A to be blank.
However,
If Cell E is a date in the future, I need Cell A to be blank. If Cell E has a date within 30 days, I need Cell A to say "CHECK". If Cell E has a date in the past, I need Cell A to say "Expired".
However,
if Cell B still has a date within 30 days (or will expire) and Cell E has a date in the future. Cell A won't be activated by Cell B.
Answers
-
@SmartsheetRookie It is difficult to understand exactly what you are trying to do without seeing the sheet or screenshots but I think I may be close to creating a solution. Check out the screenshot below and tell me if it appears to be populating as you would like.
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
This seems correct, may I please get the function line and I can test it out myself.
Thanks!
-
@SmartsheetRookie Absolutely! Just one more quick question to ensure the formula will work as expected... Will column B and column E always show a date in every row? If there are instances where a date will not be entered into one or both of these columns, I would need to adjust the formula a little bit. I also would like clarification on the importance of this conditions. This also affects how the formula works.
For example: You state that if Column D is checked return "Requested" but what if Column B is in the Past, would it show as Expired or Requested? If you could simply number them in order of importance (Check, Expired, No Need, Requested), 1 being the most important, that would be great.
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
"Will column B and column E always show a date in every row?" -Yes
"For example: You state that if Column D is checked return "Requested" but what if Column B is in the Past, would it show as Expired or Requested?" -It should show requested
" If you could simply number them in order of importance (Check, Expired, No Need, Requested), 1 being the most important, that would be great." -Column B to Column E (Least to Most Important)
-
@SmartsheetRookie The way you have explained it, the checkboxes will not have any impact on the result. Could you please explain when the checkbox columns would be reflected in the status?
This is the formula built based on your explanation of importance of the columns but I expect we will need to adjust it after I get some clarification on the checkbox columns.
=IF([Column E]@row < TODAY(), "Expired", IF([Column E]@row < TODAY(30), "CHECK", IF([Column E]@row > TODAY(), "", IF([Column D]@row = 1, "Requested", IF([Column C]@row = 1, "No Need", IF(ColumnB@row < TODAY(), "Expired", IF(ColumnB@row < TODAY(30), "CHECK", IF(ColumnB@row > TODAY(), ""))))))))
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives