IF Statements Date Range & Excluding Blank Cells

Elliot Holder

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

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.

Elliot Holder

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.