I have a spreadsheet set up in Excel (shown below) with a date column called Appointment Date. I want to highlight all of the dates in that column but only IF they fall within the next 7 days.
Firstly, we need to know what today’s date is to be able to carry out this calculation. So, in cell E1 I have typed in the following formula to give me the current date. =TODAY().
Then follow these steps to apply the formatting using a formula:-
- Highlight B2:B10 (all cells you want to conditionally format)
- Click, Home, Conditional Formatting, New Rule
- Select the rule type ‘Use a formula to determine which cells to format’
- In the Edit rule description (shown in the image below) type in the following formula:-=B2<$E$1+7
- Let’s break that down….. $E$1 + 7 is today’s date in E1 + 7 so if today is 17/1/17 Excel calculates 24/1/17. Add the B2< in front and we are asking if the date in cell B2 is less than (or earlier than) 24/1/17.
- Once you have the formula set up, click the Format button below, click the Fill tab at the top of the dialog box and set the colour to red.
- Press OK until you see all the cells containing a date within the next 7 days change colour.
- Voila 🙂