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

✭✭✭✭

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!

• ✭✭✭✭✭✭

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

• ✭✭✭✭

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

)

)