Hours outside workhours are filled with grey using conditional formattting, except weekends.
Conditional formatting formula applied on cell range C6:I29:
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:
- Weekly schedule template in excel
- Highlight specific time ranges in a weekly schedule in excel
- Find empty hours in a weekly schedule in excel
- Populate cells dynamically in a weekly schedule in excel
Download excel sample file
Highlight work hours-in-a-weekly-schedule.xlsx
(Excel 2007 Workbook *.xlsx)
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.