Conditional Formatting If Statement Using Dates in Excel

by | Jan 17, 2017 | Excel |

Conditional Formatting If Statement Using Dates in Excel

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

  1. Highlight B2:B10 (all cells you want to conditionally format)
  2. Click, Home, Conditional  Formatting, New Rule
  3. Select the rule type ‘Use a formula to determine which cells to format’
  4. In the Edit rule description (shown in the image below) type in the following formula:-=B2<$E$1+7
  5. 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.
  6. 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.
  7. Press OK until you see all the cells containing a date within the next 7 days change colour.
  8. Voila 🙂conditional formatting if statement with dates in excel