IF Statements Date Range & Excluding Blank Cells

Elliot Holder
Elliot Holder ✭✭
edited 12/09/19 in Formulas and Functions

I need to build a status column that registers when any due dates in a project are nearing certain parameters. This is for projects that could have between 1 and 10 due dates. Formula at the bottom (it's a doozy).

  • Green if any due dates in a row are > than today(+7).
  • Yellow if any due dates are between Today(+7) and today.
  • Red if any due dates are =< Today.

My difficulties are with the Yellow status and getting the formula to ignore blank cells.

  1. How do I get the if statement to recognize a date range based on today and not specific dates?
  2. How do I get the formula to ignore blank cells instead of counting them as < today.

=IF((AND([Assets Due]1 = "", [White Papers Due]1 = "", [Deck Due Date]1 = "", [Rendering Due Date]1 = "", [Client Quote Due]1 > TODAY(+7), [Printing Deadline]1 > TODAY(+7), [Comp1 Due Date]1 = "", [Comp2 Due Date]1 = "", [Comp3 Due Date]1 = "", [Comp4 Due Date]1 = "", [Comp5 Due Date]1 = "", [Comp6 Due Date]1 = "", [Comp7 Due Date]1 = "", [Comp8 Due Date]1 = "", [Comp9 Due Date]1 = "", [Comp10 Due Date]1 = "")), "Green", IF((AND([Assets Due]1 > TODAY(+7), [White Papers Due]1 > TODAY(+7), [Deck Due Date]1 > TODAY(+7), [Rendering Due Date]1 > TODAY(+7), [Client Quote Due]1 > TODAY(+7), [Printing Deadline]1 > TODAY(+7), [Comp1 Due Date]1 > TODAY(+7), [Comp2 Due Date]1 > TODAY(+7), [Comp3 Due Date]1 > TODAY(+7), [Comp4 Due Date]1 > TODAY(+7), [Comp5 Due Date]1 > TODAY(+7), [Comp6 Due Date]1 > TODAY(+7), [Comp7 Due Date]1 > TODAY(+7), [Comp8 Due Date]1 > TODAY(+7), [Comp9 Due Date]1 > TODAY(+7), [Comp10 Due Date]1 > TODAY(+7))), "Green", IF((OR([Assets Due]1 = TODAY(), [White Papers Due]1 = TODAY(), [Deck Due Date]1 = TODAY(), [Rendering Due Date]1 = TODAY(), [Client Quote Due]1 = TODAY(), [Printing Deadline]1 = TODAY(), [Comp1 Due Date]1 = TODAY(), [Comp2 Due Date]1 = TODAY(), [Comp3 Due Date]1 = TODAY(), [Comp4 Due Date]1 = TODAY(), [Comp5 Due Date]1 = TODAY(), [Comp6 Due Date]1 = TODAY(), [Comp7 Due Date]1 = TODAY(), [Comp8 Due Date]1 = TODAY(), [Comp9 Due Date]1 = TODAY(), [Comp10 Due Date]1 = TODAY())), "Yellow", IF((OR([Assets Due]1 <= TODAY(), [White Papers Due]1 <= TODAY(), [Deck Due Date]1 <= TODAY(), [Rendering Due Date]1 <= TODAY(), [Client Quote Due]1 <= TODAY(), [Printing Deadline]1 <= TODAY(), [Comp1 Due Date]1 <= TODAY(), [Comp2 Due Date]1 <= TODAY(), [Comp3 Due Date]1 <= TODAY(), [Comp4 Due Date]1 <= TODAY(), [Comp5 Due Date]1 <= TODAY(), [Comp6 Due Date]1 <= TODAY(), [Comp7 Due Date]1 <= TODAY(), [Comp8 Due Date]1 <= TODAY(), [Comp9 Due Date]1 <= TODAY(), [Comp10 Due Date]1 <= TODAY())), ""))))

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Take a gander at this walkthrough...

    [Assets Due]@row:[Comp10 Due Date]@row

    =JOIN(COLLECT(range to collect, based on criteria range, criteria))

    This can be used to pull together all of the dates across the range that meet a specified criteria. If no dates within the range meet the criteria, it will return a blank.

     

    We could then use the NOT function to basically say that if the JOIN/COLLECT is not blank, then it means something within the range met the criteria.

     

    We then use an IF statement to say that if something meets xyz criteria, return "this".

    .

    So here goes...

    .

    =JOIN(COLLECT(range to collect, based on criteria range, criteria))

     

    JOIN(COLLECT([Assets Due]@row:[Comp10 Due Date]@row, [Assets Due]@row:[Comp10 Due Date]@row, criteria))

     

    =IF(NOT(ISBLANK(JOIN(COLLECT([Assets Due]@row:[Comp10 Due Date]@row, [Assets Due]@row:[Comp10 Due Date]@row, criteria)))), "this")

    .

    There's the basic idea on how we are going to generate the colors. Now we write it out 3 times. One for each color.

    .

    So what are our colors and their respective criteria?

     

    Red: Is a date and is less than or equal to today.

    Yellow: Is a date and is within the next 7 days.

    Green: Is a date and is more than 7 days away.

    .

    Red: JOIN(COLLECT(collection range, criteria range, AND(ISDATE(@cell), @cell <= TODAY())))

    Yellow: JOIN(COLLECT(collection range, criteria range, AND(ISDATE(@cell), @cell > TODAY(), @cell <= TODAY(7))))

    Green: JOIN(COLLECT(collection range, criteria range, AND(ISDATE(@cell), @cell >TODAY(7))))

    .

    Red: =IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Red", else)

    Yellow: =IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Yellow", else)

    Green: =IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Green", else)

    .

    Nest them dropping the next IF into the "else" portion of the previous IF.

     

    =IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Red", IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Yellow", IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Green", else)))

    .

    We will say to return a blank if none of the 3 color criteria are met (remaining "else").

     

    =IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Red", IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Yellow", IF(NOT(ISBLANK(JOIN(COLLECT(................)))), "Green", "")))

    .

    And that gives us a final solution of...

     

    =IF(NOT(ISBLANK(JOIN(COLLECT([Assets Due]@row:[Comp10 Due Date]@row, [Assets Due]@row:[Comp10 Due Date]@row, AND(ISDATE(@cell), @cell <= TODAY()))))), "Red", IF(NOT(ISBLANK(JOIN(COLLECT([Assets Due]@row:[Comp10 Due Date]@row, [Assets Due]@row:[Comp10 Due Date]@row, AND(ISDATE(@cell), @cell > TODAY(), @cell <= TODAY(7)))))), "Yellow", IF(NOT(ISBLANK(JOIN(COLLECT([Assets Due]@row:[Comp10 Due Date]@row, [Assets Due]@row:[Comp10 Due Date]@row, AND(ISDATE(@cell), @cell >TODAY(7)))))), "Green", "")))

    .

    See if this will work for you and let me know.

  • I was not able to get that to work correctly. I instead summarized each possible state into 3 separate columns. One each for Due Today, Past Due, and Not Due Yet. I managed to get these to work well and am building a simpler formula to set the color status. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!