Drive Status Indicators on [End Date] vs. Today, considering only Workdays

Cory Strischek

Hello SmartPeople,

I use a formula below to show red, yellow, green and blue based on less than 100% completion when end date is in X days.

• If complete, blue.
• If incomplete/ends tomorrow, red.
• If incomplete/ends in two days, yellow.
• If incomplete/ends in three or more days, green.

My program does not count weekends. How can my formula consider only workdays?

# End Status

=IF(

  [% Complete]@row = 1, "Blue", 

  IF(AND([% Complete]@row < 1, [Revised End Date]@row <= TODAY(1) ), "Red", 

  IF(AND([% Complete]@row < 1, [Revised End Date]@row <= TODAY(2) ), "Yellow", 

  "Green"

)

Thank you!

Paul Newcome

Happy to help! 👍️

When I put the above together, I was only focusing on the specific NETWORKDAYS function and getting it in the mix. Now that I take another look at your formula, I see where we could actually make this a little more efficient.

When using nested IF statements, the formula will stop on the first TRUE. So if it makes it to the second IF then the first MUST be false. If it makes it to the third IF then the first and second MUST be false. So on and so forth...

Having said that, Since your first argument is

[% Complete]@row = 1

anything that comes after that has already implied that [% Complete]@row is less than 1 which means you don't actually need to include that criteria in the IF statements that come after it. That is the same logic where you specified less than or equal to two for red but then only specified less than or equal to three for the yellow criteria. You didn't need to specify greater than two in the yellow criteria.

So that means we can actually get rid of your AND statements which saves some keystrokes and gives the sheet less to do on the back-end.

=IF([% Complete]@row = 1, "Blue", IF(NETWORKDAYS(TODAY(), [Revised End Date]@row) <= 2, "Red", IF(NETWORKDAYS(TODAY(), [Revised End Date]@row) <= 3, "Yellow", "Green")))

Hope that makes sense...

Paul Newcome

You are going to want to work in a NETWORKDAYS function.

=IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, NETWORKDAYS([Revised End Date]@row, TODAY()) <= 1), "Red", IF(AND([% Complete]@row < 1, NETWORKDAYS([Revised End Date]@row, TODAY()) <= 2), "Yellow", "Green")

Cory Strischek

Thanks! Yes, networkdays seems like the way to go!

For my own mental health, I spun it around a bit.

=IF(  

 [% Complete]@row = 1, "Blue",  

 IF(AND([% Complete]@row < 1, NETWORKDAYS(TODAY(), [Revised End Date]@row) <= 2), "Red", 

 IF(AND([% Complete]@row < 1, NETWORKDAYS(TODAY(), [Revised End Date]@row) <= 3), "Yellow", 

 "Green")

 )

)

Paul Newcome

Happy to help! 👍️

When I put the above together, I was only focusing on the specific NETWORKDAYS function and getting it in the mix. Now that I take another look at your formula, I see where we could actually make this a little more efficient.

When using nested IF statements, the formula will stop on the first TRUE. So if it makes it to the second IF then the first MUST be false. If it makes it to the third IF then the first and second MUST be false. So on and so forth...

Having said that, Since your first argument is

[% Complete]@row = 1

anything that comes after that has already implied that [% Complete]@row is less than 1 which means you don't actually need to include that criteria in the IF statements that come after it. That is the same logic where you specified less than or equal to two for red but then only specified less than or equal to three for the yellow criteria. You didn't need to specify greater than two in the yellow criteria.

So that means we can actually get rid of your AND statements which saves some keystrokes and gives the sheet less to do on the back-end.

=IF([% Complete]@row = 1, "Blue", IF(NETWORKDAYS(TODAY(), [Revised End Date]@row) <= 2, "Red", IF(NETWORKDAYS(TODAY(), [Revised End Date]@row) <= 3, "Yellow", "Green")))

Hope that makes sense...