Color ball formula that measures date range across 5 columns to determine color based workdays

Adam Kinney

1) "Grey" = no date in [Last Activity Date]@row.

2) "Red" if date in MIN[Initial Presentation]@row through MAX[Qualified Win]@row is greater than or equal to 30 working days from [Last Activity Date]@row not including holidays.

3) "Yellow" if date in MIN[Initial Presentation]@row through MAX[Qualified Win]@row is between 15 and 29 working days from [Last Activity Date]@row not including holidays.

4) "Green" if date in MIN[Initial Presentation]@row through MAX[Qualified Win]@row is between 15 working days or less from [Last Activity Date]@row not including holidays OR [Qualified Win]@row is a date.

Know it can be done just having trouble figuring it out. Thank you!

Genevieve P.

OK no problem! So instead of looking at the [Last Activity Date]@row, you want to measure from TODAY.

You're almost correct in your statement above, however you don't need an OR if you only need to find one date from the range to measure against.

It also sounds like you want the MAX instead of the MN. The MIN will give you the earliest date, so October vs. November. The MAX will give you the latest date.

The final thing to adjust is that NETWORKDAYS needs the two dates with a comma between ([Start Date],[End Date]) vs. having a minus sign. In this instance, TODAY will always be the "end date" part of this statement, so you'll want to have it in second place.

Try this:

=IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Qualified Win]@row), TODAY()) >= 30, "Red")

Adjusted formula:

=IF([Last Activity Date]@row = "", "Gray", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Qualified Win]@row), TODAY()) >= 30, "Red", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Qualified Win]@row), TODAY()) >= 15, "Yellow", IF(ISDATE([Qualified Win]@row), "Green", "Green"))))

In looking at this again, I recognize that we put the ISDATE for "Qualified Win" as the. last statement. This means that even if there's a date there, the formula will first look at the difference in the network days between the date there and Today... so if you have a date in that cell from a row that's been completed months ago, it will first turn RED before even looking to see if it should be Green.

Should your range only go to the second-last column?

Genevieve P.

Hi @Adam Kinney

We'd want to change the order around, and put the Green statement at the beginning. You're receiving an error because it's looking for a MAX within a range that is completely blank...

Try this:

=IF([Last Activity Date]@row = "", "Gray", IF(ISDATE([Qualified Win]@row), "Green", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Verbal Agreement]@row), TODAY()) >= 30, "Red", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Verbal Agreement]@row), TODAY()) >= 15, "Yellow", "Green"))))

Did that do it?

Adam Kinney

@Genevieve P have another one for you

Genevieve P.

Hi @Adam Kinney

Thanks for the tag! Would you mind explaining what you mean by

MIN[Initial Presentation]@row through MAX[Qualified Win]@row

Are you wanting to look through the range [Initial Presentation]@row to [Qualified Win]@row for either the MIN or the MAX date? Or do you need to find both?

Genevieve P.

Hi @Adam Kinney

I've created something, but I'm not sure it will do what you're looking for as it will depend on the MIN/MAX situation.

You can use the function NETWORKDAYS to figure out the days between two dates... in your case, between the Last Activity date and either a MIN or a MAX of the date ranges through the other columns. In the formula below, I've created OR statements to look for if either the MIN of those dates OR the MAX of those dates fits your criteria.

I've broken down the statements for each of your rules below, with the final formula at the bottom.

GRAY

1) "Grey" = no date in [Last Activity Date]@row.

=IF([Last Activity Date]@row = "", "Gray"

RED

2) "Red" if date in MIN[Initial Presentation]@row through MAX[Qualified Win]@row is greater than or equal to 30 working days from [Last Activity Date]@row not including holidays.

IF(OR(NETWORKDAYS([Last Activity Date]@row, MIN([Initial Presentation]@row:[Qualified Win]@row)) >= 30, NETWORKDAYS([Last Activity Date]@row, MAX([Initial Presentation]@row:[Qualified Win]@row)) >= 30), "Red"

YELLOW

3) "Yellow" if date in MIN[Initial Presentation]@row through MAX[Qualified Win]@row is between 15 and 29 working days from [Last Activity Date]@row not including holidays.

=IF(OR(NETWORKDAYS([Last Activity Date]@row, MIN([Initial Presentation]@row:[Qualified Win]@row)) >= 15, NETWORKDAYS([Last Activity Date]@row, MAX([Initial Presentation]@row:[Qualified Win]@row)) >= 15), "Yellow"

*Note that since you already have a rule for >= 30 days, you don't need to specify when this range stops, as the 30 day rule will apply in the earlier logic statement. Logic statements read left-to-right and will stop as soon as a criteria is met, so it will read the 30-day statement first.

GREEN

4) "Green" if date in MIN[Initial Presentation]@row through MAX[Qualified Win]@row is between 15 working days or less from [Last Activity Date]@row not including holidays OR [Qualified Win]@row is a date.

IF(ISDATE([Qualified Win]@row), "Green", "Green"

FULL FORMULA

=IF([Last Activity Date]@row = "", "Gray", IF(OR(NETWORKDAYS([Last Activity Date]@row, MIN([Initial Presentation]@row:[Qualified Win]@row)) >= 30, NETWORKDAYS([Last Activity Date]@row, MAX([Initial Presentation]@row:[Qualified Win]@row)) >= 30), "Red", IF(OR(NETWORKDAYS([Last Activity Date]@row, MIN([Initial Presentation]@row:[Qualified Win]@row)) >= 15, NETWORKDAYS([Last Activity Date]@row, MAX([Initial Presentation]@row:[Qualified Win]@row)) >= 15), "Yellow", IF(ISDATE([Qualified Win]@row), "Green", "Green"))))

Please let me know if this was what you were trying to do! You can test out each statement individually, and you can even test out the small pieces (such as just the NETWORKDAYS bits) to ensure it's calculating correctly.

Cheers!

Genevieve

Adam Kinney

@Genevieve P I need the formula to recognize the dates as they are populated from left to right across the row as highlighted below. Meaning the number of days that trigger the Gray-Red-Yellow-Green ball are based on the date farthest to the right in the range. Make sense?

Genevieve P.

Thank you for clarifying! In this instance you’d only need to find the MAX date from that range then, assuming they increase as the dates are input to the right, is that correct ?

Adam Kinney

@Genevieve P Thank you. The piece I am missing is the number of days should be based on TODAY()-the date that is the farthest right in the row.

Something like this for each statement to achieve this?

IF(OR(NETWORKDAYS(TODAY() - MIN([Initial Presentation]@row:[Qualified Win]@row)) >= 30

Genevieve P.

OK no problem! So instead of looking at the [Last Activity Date]@row, you want to measure from TODAY.

You're almost correct in your statement above, however you don't need an OR if you only need to find one date from the range to measure against.

It also sounds like you want the MAX instead of the MN. The MIN will give you the earliest date, so October vs. November. The MAX will give you the latest date.

The final thing to adjust is that NETWORKDAYS needs the two dates with a comma between ([Start Date],[End Date]) vs. having a minus sign. In this instance, TODAY will always be the "end date" part of this statement, so you'll want to have it in second place.

Try this:

=IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Qualified Win]@row), TODAY()) >= 30, "Red")

Adjusted formula:

=IF([Last Activity Date]@row = "", "Gray", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Qualified Win]@row), TODAY()) >= 30, "Red", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Qualified Win]@row), TODAY()) >= 15, "Yellow", IF(ISDATE([Qualified Win]@row), "Green", "Green"))))

In looking at this again, I recognize that we put the ISDATE for "Qualified Win" as the. last statement. This means that even if there's a date there, the formula will first look at the difference in the network days between the date there and Today... so if you have a date in that cell from a row that's been completed months ago, it will first turn RED before even looking to see if it should be Green.

Should your range only go to the second-last column?

Adam Kinney

That makes sense. Yes, would want the color to remain green permanently if the [Qualified Win] column has a date.

So if I adjust the formula to:

=IF([Last Activity Date]@row = "", "Gray", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Verbal Agreement]@row), TODAY()) >= 30, "Red", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Verbal Agreement]@row), TODAY()) >= 15, "Yellow", IF(ISDATE([Qualified Win]@row), "Green", "Green"))))

Should do the trick? I tested it and a row that has a date in the qualified win column only it is showing #INVALID DATA TYPE error with the change to the MAX value.

Genevieve P.

Hi @Adam Kinney

We'd want to change the order around, and put the Green statement at the beginning. You're receiving an error because it's looking for a MAX within a range that is completely blank...

Try this:

=IF([Last Activity Date]@row = "", "Gray", IF(ISDATE([Qualified Win]@row), "Green", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Verbal Agreement]@row), TODAY()) >= 30, "Red", IF(NETWORKDAYS(MAX([Initial Presentation]@row:[Verbal Agreement]@row), TODAY()) >= 15, "Yellow", "Green"))))

Did that do it?

Adam Kinney

Problem solved once again. Thank you!

Genevieve P.

Wonderful! I'm glad we could come up with a solution together.

https://media.giphy.com/media/3o6Mb9EC7mNqXl9x7y/giphy.gif