## How to highlight weekends [Conditional Formatting]

The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays and Sundays.

Conditional formatting formula

### How to apply conditional formatting to cell range C3:C20

- Select cell range C3:C20.
- Go to tab "Home" on the ribbon.
- Click the "Conditional Formatting" button.
- Click "New Rule.." to open a dialog box.

- Click "Use a formula to determine which cells to format".
- Type the formula. (See above).
- Click "Format..." button and choose a formatting.
- Click OK button twice.

### Explaining conditional formatting formula

#### Step 1 - Identify day of the week

The WEEKDAY function returns 1 to 7 based on a date's day of the week, the second argument tells the function which day a week begins on. Using 2 as the second argument makes the week start on a Monday.

WEEKDAY(C3,2)

becomes

WEEKDAY(43101,2)

and returns 1.

The number calculated by the WEEKDAY function tells us which day of week a date is:

1 - Monday

2 - Tuesday

3 - Wednesday

4 - Thursday

5 - Friday

6 - Saturday

7 - Sunday

#### Step 2 - Check if the number is larger than 5

The larger than sign checks if the weekday function is larger than 5. If the number is larger than 5 then it must be a Saturday or Sunday, see list above.

WEEKDAY(C3,2)>5

becomes

1>5 and returns FALSE.

#### Step 3 - Boolean value determines if the cell is highlighted

If the logical expression returns TRUE then the cell is highlighted and FALSE nothing happens.

### How to highlight record when a date falls on a weekend

Conditional formatting formula applied to cell range B3:D20

This formula is almost as the first formula, except there is a dollar sign in front of the B (column number). This makes the column absolute meaning the cell reference doesn't change when the Conditional Formatting moves to the next column. It only changes when it moves to a new row.

Example,

Cell |
Formula |

B3 | =WEEKDAY($B3,2)>5 |

C3 | =WEEKDAY($B3,2)>5 |

D3 | =WEEKDAY($B3,2)>5 |

B4 | =WEEKDAY($B4,2)>5 |

C4 | =WEEKDAY($B4,2)>5 |

D4 | =WEEKDAY($B4,2)>5 |

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

How to highlight MAX and MIN value based on month

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]

Highlight date ranges overlapping selected record [VBA]

The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional […]

How to highlight dates based on day of week

The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]

Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Color even months Conditional formatting formula: =NOT(MOD(MONTH($B6),2)) Color odd years Conditional formatting formula: […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form