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

Options

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!


Tags:

Best Answers

Answers

  • Adam Kinney
    Adam Kinney ✭✭✭✭
    Options

    @Genevieve P have another one for you

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Adam Kinney ✭✭✭✭
    edited 11/05/20
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    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
    Adam Kinney ✭✭✭✭
    edited 11/05/20
    Options

    @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

  • Adam Kinney
    Adam Kinney ✭✭✭✭
    Options

    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.


  • Adam Kinney
    Adam Kinney ✭✭✭✭
    Options

    Problem solved once again. Thank you!



  • Genevieve P.
    Genevieve P. Employee Admin
    Options

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