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
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
Highlight date ranges overlapping selected record [VBA]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
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) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]
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.