IF and dates functions

SmartsheetRookie · 08/10/22

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.

Melissa Boehl · 08/10/22

@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.

SmartsheetRookie · 08/11/22

https://community.smartsheet.com/discussion/comment/339252#Comment_339252

This seems correct, may I please get the function line and I can test it out myself.

Thanks!

Melissa Boehl · 08/11/22

@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.

SmartsheetRookie · 08/12/22

@Melissa Boehl

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

Melissa Boehl · 08/15/22

@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(), ""))))))))

IF and dates functions

Answers

Categories