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

### Download Excel file

Populate cells dynamically in a weekly schedule

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

Highlight specific time ranges in a weekly schedule

In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]

I would like to share this simple weekly schedule I created. How to use weekly schedule Type any date in cell […]

This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]

Watch schedule that populates vacation time

This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]

Calendar with scheduling [vba]

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]

Extract dates from a cell block schedule

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]

This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]

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

Download Excel *.xlsx file

Highlight-work-hours-in-a-weekly-schedulev2.xlsx