Highlight a date occuring [Conditional formatting]
Excel has a built-in feature that allows you to highlight dates if a given condition is met.The date conditions you can choose from are:
- Yesterday
- Today
- Tomorrow
- In the last 7 days
- Last week
- This week
- Next week
- Last month
- This month
- Next month
How to apply Conditional Formatting
- Select cell range containing dates.
- Go to tab "Home" on the ribbon if you are not already there.
- Click "Conditional formatting" button.
- Click on "Highlight Cells Rules".
- Click "A Date Occuring..."
- A dialog box appears that lets you specify the date condition and the formatting.
- Pick a prebuilt formatting or use custom format to create a new one.
- Click OK button.
Highlight rows/records
You need to use a formula instead of the prebuilt ones in order to highlight the entire row if date meets the condition.
- 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 below which formula to use).
- Click "Format..." button and choose a formatting.
- Click OK button twice.
Highlight row if the date is yesterday
The following formula highlights all cells on the same row if the date in column D is yesterday:
The $ (dollar sign) makes the cell reference absolute meaning it locks the column thus highlighting all cells on the same row if the date matches the condition.
Highlight row if the date is today
Highlight row if the date is tomorrow
Highlight row if the date is in the last 7 days
Highlight row if the date is in last week
Change the second argument in WEEKNUM function if the week doesn't begin with Sunday.
Highlight row if the date is in this week
Change the second argument in WEEKNUM function if the week doesn't begin with Sunday.
Highlight row if the date is in next week
Change the second argument in WEEKNUM function if the week doesn't begin with Sunday.
Highlight row if the date is in last month
Highlight row if the date is in this month
Highlight row if the date is in next month
Excel lets you easily highlight values based on a condition you specify, with a built-in formatting or a custom formatting. […]
Highlight unique/duplicates [Conditional formatting]
Excel has a few built-in conditional formatting features, one of them highlights values that have at least one duplicate. The […]
Highlight cells containing string [Conditional formatting]
Excel allows you to quickly highlight cells containing a given text string. How to apply Conditional Formatting Select cell range. […]
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.