Setting up your work hours in a weekly schedule
The image above demonstrates conditional formatting highlighting hours outside work hours, those cells are filled with grey 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.
Explaining conditional formatting in cell C9
Step 1 - Check if time $B6 is less than start hour $C$31
$B6<$C$31
becomes
0<0.291666666666667
and returns TRUE.
Step 2 - Check if time $B6 is larger than or equal to start hour $C$32
$B6>=$C$32
becomes
0>=0.708333333333333
and returns FALSE
Step 3 - If any of the logical expressions evaluate to TRUE then return TRUE
The OR function checks whether any of the arguments are TRUE or FALSE and returns FALSE if all arguments are FALSE.
OR($B6<$C$31, $B6>=$C$32)
becomes
OR(TRUE, FALSE)
and returns TRUE.
Step 4 - Check if weekday is less than 7 (Saturday)
The WEEKDAY function returns a number from 1 to 7 identifying the day of the week of a date.
WEEKDAY(C$4)<7
becomes
WEEKDAY(40391)<7
and returns TRUE.
Step 5 - Check if weekday number is larger than 1 (Sunday)
WEEKDAY(C$4)>1
becomes
WEEKDAY(40391)<1
and returns FALSE.
Step 6 - Both logical expressions must be TRUE
The AND function checks whether all arguments are TRUE and returns TRUE if all arguments are TRUE.
AND(WEEKDAY(C$4)<7, WEEKDAY(C$4)>1)
becomes
AND(TRUE, FALSE)
and returns FALSE.
Step 7 - AND logic
AND(OR($B6<$C$31, $B6>=$C$32), AND(WEEKDAY(C$4)<7, WEEKDAY(C$4)>1))
becomes
AND(TRUE, FALSE)
and returns FALSE. Cell C6 is not highlighted.
Schedule category
In this post I am going to add one more function to the weekly schedule I built in a previous […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]
Excel categories
2 Responses to “Setting up your work hours in a weekly schedule”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
Is there a way to have the Title display only once for a given duration? For instance, 'Strategies' appears 4 times. Is it possible to have 'Strategies' appear only in the cell for the Start time (cell F18)?
Hi Sarah
Yes, it is possible.
Get Excel *.xlsx file
Highlight-work-hours-in-a-weekly-schedulev2.xlsx