Author: Oscar Cronquist Article last updated on February 13, 2018

Hours outside workhours are filled with grey using conditional formattting, except weekends.

Conditional formatting formula applied on cell range C6:I29:

=AND(OR($B6<$C$31, $B6>=$C$32), AND(WEEKDAY(C$4)<7, WEEKDAY(C$4)>1))

This formula checks if the weekday in C4:I4 is 2,3,4,5 or 6. (Monday to Friday) and if the time in cell range B6:B29 is outside workhours specified in cell C31 and C32. If formula returns TRUE, the cell is filled grey.

Previous blog posts about weekly schedule:

Download excel sample file
Highlight work hours-in-a-weekly-schedule.xlsx
(Excel 2007 Workbook *.xlsx)

Named ranges

Start (E2:E5)
End (F2:F5)
Title (B3:B5)

Functions in this article:

Returns a number from 1 to 7 identifing the day of the week of a date

AND(logical1, logical2, ..)
Checks whether all arguments are TRUE and returns TRUE if all arguments are TRUE

OR(logical1, logical2, ..)
Checks whether any of the arguments are TRUE or FALSE and returns FALSE if  all arguments are FALSE.