## 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.

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 […]

### 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.

**Contact Oscar**

You can contact me through this contact form

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