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]
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 [โฆ]
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A [โฆ]
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The [โฆ]
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.
English
Spanish
