IF and dates functions

Options
SmartsheetRookie
edited 08/10/22 in Smartsheet Basics

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

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    edited 08/10/22
    Options

    @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

  • SmartsheetRookie
    Options

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

    Thanks!

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    Options

    @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

  • SmartsheetRookie
    edited 08/12/22
    Options

    @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
    Melissa Boehl ✭✭✭✭✭✭
    Options

    @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