Excel Conditional Formatting Based on Date (9 Examples)

Example 1 – Using Built-In Date Rules

We’ll format the rows where the joining dates are within the past 7 days (Current date: 25-10-22).

Steps:

  • We stored the name of employees and their joining dates in the dataset.

  • Select the cells you want to apply the conditional formatting on (In our case, Range D5:D9).
  • Go to Home and select the Conditional Formatting option under the Style section.
  • Select the Highlight Cell Rules option and select the A Date Occurring option.

Conditional formatting with date occurring

  • A new window named A Date Occurring should appear.
  • Select the In the last 7 days option from the first drop-down menu.

Conditional formatting with default date options

  • Select the default color for highlighting cells.

  • Hit OK.

We want to highlight the dates of the last month.

  • Go to the A Date Occurring window as shown previously.
  • Choose the Last Month option from the drop-down list.

  • Click on the drop-down symbol for the highlighting color.
  • Choose the Custom Format option.

Conditional formatting with custom format

  • The Format Cells window appears.
  • Go to the Font tab.
  • Choose Bold as the desired Font style.

  • Move to the Fill tab.
  • Choose the desired color from the list.
  • Press the OK button.

  • Here’s the result.

In short in this section, we get options for yesterday, today, tomorrow, last week, this week, next week, last month, this month, and next month. We can avail of those options without using any other formula or technique.

Alternative Method:

Steps:

  • Click on the drop-down list of Conditional Formatting.
  • Click on the New Rule option.

Conditional formatting with new rule

  • The New Formatting Rule window appears.
  • Select Format only cells that contain.
  • Go to Edit the Rule Description section.
  • Choose the Dates Occurring option from the list.

  • You get a new drop-down field beside the previous section.
  • Click on the down arrow.

  • Choose the Last week option.
  • Click on the Format option.

Conditional formatting dates from last week

  • Choose the desired Font and Fill color from the Format Cells window.
  • Press the OK button.

  • Go back to the previous window and see the Preview of the result.

  • Click on the OK button.

Read More: Conditional Formatting Based on Date in Another Cell in Excel


Example 2 – Highlight Dates Preceding the Current Date Using the NOW or TODAY Function

We want to format the cells and highlight the date expired products based on the current date (25/10/22). We’ll highlight the cells with two colors: one for the date expired products and another one for products within the expiry date.

Steps:

  • Select the cells you want to apply the conditional formatting on (In our case, B5:D9).
  • Go to Home and select the Conditional Formatting option under the Style section.
  • Select the New Rule option from the drop-down menu.

  • A new window named New Formatting Rule should appear.
  • Select the Use a formula to determine which cells to format rule type.
  • Enter the following formula in the specified field.
=$D5<NOW() 
  • Select the Format feature.

Conditional formatting with NOW function

=$D5<NOW() checks whether the dates in Column D are less than the current date. If the date satisfies the conditions, then it formats the cell)

  • Select the desired format (see Example 1) and click OK.

  • Press the OK button.

We want to highlight cells with future dates.

  • Go to the New Formatting Rule window.
  • Put the following formula for products with a future date.
=$D5>Today()
  • We also formatted the highlighting color from the format section.

Conditional formatting with TODAY function

  • Press the OK button.

Read More: Apply Conditional Formatting for Dates Older Than Today in Excel


Example 3 – Using the WEEKDAY Function to Highlight Specific Days of a Week

We highlighted the weekends of the first two weeks of April 2021 in the calendar using the WEEKDAY function.

Steps:

  • Select the cells you want to apply the conditional formatting on (In our case, C7:L11).

  • Go to the New Formatting Rule window by following the steps of Example 2.
  • Select the Use a formula to determine which cells to format rule type.

  • Enter the following formula in the specified field.
  =WEEKDAY(C$8,2)>5  
  • Select the desired format by following the steps in Example 1.

Conditional formatting with WEEKDAY function

Explanation:

=WEEKDAY(C$8,2)>5 only returns a TRUE value when the days are Saturday (6) and Sunday (7) and formats the cells accordingly.

  • Press the OK button.

Read More: Highlight Row with Conditional Formatting Based on Date in Excel


Example 4 – Highlight Dates Within a Date Range Using the AND Rule in Conditional Formatting

We have formatted the rows where the joining dates are between two different dates. We will highlight the cells with the joining date between the start and the end date.

Steps:

  • Select the cells you want to apply the conditional formatting on (B8:D12).

  • Go to the New Formatting Rule window by following the steps of Example 2.
  • Select the Use a formula to determine which cells to format rule type.
  • Enter the condition/formula in the specified field
  =AND($D8>=$C$4, $D8<=$C$5)
  • Select the desired format by following the steps from Example 1.

Conditional formatting with date in a range

Explanation:

=AND($D13>=$C$4, $D13<=$C$6) checks whether the dates in Column D are greater than the C4 cell’s date and less than the C6 cell’s date. If the date satisfies the conditions, then it formats the cell).

  • Press the OK button.

Alternate Method:

  • Select the range B8:D12.
  • Choose Highlight Cells Rules from the Conditional Formatting drop-down.
  • Click on the Between option from the list.

  • A dialog box will appear named Between.
  • Put the cell reference of the start date on the box marked as 1 and the end date on the box marked as 2.

  • Press the OK button.

The first method modifies the color of the whole row based on the condition, but the alternative method is applicable to the cells only.


Example 5 – Highlight Holidays with the MATCH or COUNTIF Function in Conditional Formatting

Steps:

  • Add a list of holidays of April 2021 to the dataset.

  • Select the range C7:L11.

  • Follow the steps of Example 2 and enter the following formula on the marked field.
  =MATCH(C$7,$C$14:$C$16,0)

Conditional formatting with MATCH function

  • Press the OK button.

  • Here’s an alternative formula you can use:
=COUNTIF($C$14:$C$16,C$7)>0

Read More: How to Change Cell Color Based on Date Using Excel Formula


Example 6 – Excel Conditional Formatting Based on Date Older Than 1 Year

Steps:

  • Select range D5:D9, which contains dates only.
  • Select the Less Than option from the Highlight Cells Rules section.

Conditional formatting with Less Than option

  • The Less Than window appears.
  • Put the following formula:
=TODAY()-365 

Conditional formatting for less than one year

  • Press the OK button.

Read More: Excel Conditional Formatting for Dates within 30 Days


Example 7 – Excel Conditional Formatting Based on a Date Less Than 6 Months from Today

Steps:

  • Select the range D5:D9.

  • Follow the steps of Example 2.
  • Insert the following formula:
=DATEDIF($D5,TODAY(),''m'')<6 
  • Define the format of highlighted cells as shown in Example 1.

Conditional formatting with DATEDIF function

  • Press the OK button.

Read More: Apply Conditional Formatting to Overdue Dates in Excel


Example 8 – Excel Conditional Formatting If It’s 15 Days After a Due Date

Steps:

  • Select the cells of the Joining Date column.

  • Follow the steps of Example 2 and go to the New Formatting Rule section.
  • Put the following formula in the box:
=TODAY()-$D5>15 

Conditional formatting with past due date

  • Press the OK button.


Example 9 – Conditional Formatting Based on the Date in Another Column

We will apply conditional formatting on the Actual Delivery Date column based on the Expected Delivery Date.

Steps:

  • Select the range B5:C9.

  • Go to the New Formatting Rule section as shown in Example 2.
  • Put the following formula in the box:
=$C5>$D5

Conditional formatting based on another column

  • Press the OK button.

Read More: Conditional Formatting Entire Column Based on Another Column in Excel


Download the Practice Workbook


Excel Conditional Formatting Based on Date: Knowledge Hub

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Chinmoy Mondol
Chinmoy Mondol

Chinmoy Mondol is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find him immersed... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo