How to Use Conditional Formatting in Excel Based on Dates

Conditional formatting has been used in Excel for quite some time now. But what is conditional formatting, and how can it benefit you? The process of putting up the conditions that decide the formatting applied to a column or a row is known as conditional formatting. It helps to present the data in a more organized manner. In this article, I have discussed different ways for applying this conditional formatting based on dates.

This article is strictly focused on the conditional formatting of dates. If you want to learn about conditional formatting in general, then you check out this article.

Download the Workbook

You can download the workbook that I used in this article from below and practice with it by yourself.

4 Different Ways to Apply Conditional Formatting Based on Dates

1. Using the Built-In Date Rules

There are some built-in date rules in the Conditional Formatting option which provides 10 different conditions to format selected cells based on the current date. In this method, I have used one of these ten rules to format the rows where the joining dates are within the past 7 days (Current date: 22-06-21).

Conditional formatting using the built-in date rules

Follow these steps to apply this solution:

  1. Select the cells you want to apply the conditional formatting on (In my case, B7:D11).Selecting the data
  2. Go to Home and select the Conditional Formatting option under the Style section.
  3. Select the Highlight Cell Rules option first and then select the A Date Occurring option from there.Selecting the A Date Occurring Option
  4. A new window named A Date Occurring should appear. Select the In the last 7 days option from the first drop-down menu.Selecting the Last 7 Days rule
  5. Select the Custom Format option from the second drop-down menu and choose your desired format. (In my case, Font Style: Bold, Fill: Yellow).Selecting the Custom Format optionSelecting the Font Style (Bold)Selecting the Fill Color (Yellow)

It will format the selected cells according to the selected format. The condition will be handled automatically by Excel. You can choose the other nine built-in options as per your needs.

2. Using the Current Date

This method shows how you can apply conditional formatting in selected cells based on the current date. There are two popular ways of getting the current date in MS Excel

  1. Using the TODAY() function – It returns the current date.
  2. Using the NOW() function – It returns the current date with the current time.

Here, I have formatted the cells and highlighted the date expired products based on the current date (22-06-21). I have used the NOW() function in this method but you can use the TODAY() function instead of NOW() as well. It will give the same result.

Conditional formatting using the current date

Follow these steps to apply this solution:

  1. Select the cells you want to apply the conditional formatting on (In my case, B9:C19).Selecting the data
  2. Go to Home and select the Conditional Formatting option under the Style section.
  3. Select the New Rule option from the drop-down menu.Selecting the New Rule option
  4. A new window named New Formatting Rule should appear. Select Use a formula to determine which cells to format rule type.
  5. Enter the condition/formula in the specified field (In my case, =$D7<NOW()) and select the Format option.Entering the formula

Explanation: The dollar sign ($)  is known as the Absolute Symbol. It makes the cell references absolute and doesn’t allow any changes. You can lock a cell by selecting the cell and pressing the F4 button.

Here, =$D7<NOW() this formula checks whether the dates in column D are less than the current date. If the date satisfies the conditions, then it formats the cell)

  1. A new window named Format Cells should appear. Select your desired format and click OK (In my case, Font Style: Bold, Fill: Yellow).Selecting the Font Style (Bold)Selecting the Fill Color (Yellow)

It will format the selected cells according to the condition and selected format.

3. Using the “WEEKDAY” function

This method introduces you to the WEEKDAY function and shows how you can use it to highlight weekends in a calendar.

The WEEKDAY function gets the day of the week as a number. Its syntax is as follows:

=WEEKDAY (serial_number, [return_type])

serial_number: The date of the week

return_type [Optional]: Number that determines the week type. By default, the value is one.

Here, I have highlighted the weekends of the first two weeks of April 2021 in the calendar using the WEEKDAY function.

Conditional formatting using the "WEEKDAY" function

Follow these steps to apply this solution:

  1. Select the cells you want to apply the conditional formatting on (In my case, B11:L15).Selecting the data
  2. Now, go to the New Formatting Rule window by following the Step-2 and Step-3 of Method-2. Select Use a formula to determine which cells to format rule type.
  3. Enter the condition/formula in the specified field (In my case, =WEEKDAY(C$12,2)>5) and select the desired format by following the Step-6 of Method-2 and click OK.Entering the formula

Explanation: The dollar sign ($) is known as the Absolute Symbol. It makes the cell references absolute and doesn’t allow any changes. You can lock a cell by selecting the cell and pressing the F4 button.

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

It will format the selected cells according to the condition and selected format.

➥ Read More: Excel Conditional Formatting Dates

4. Within a Date Range

This method shows how you can apply conditional formatting in selected cells within a certain range of dates.

Here, I have formatted the rows where the joining dates are between two different dates.

Conditional formatting within a date range

Follow these steps to apply this solution:

  1. Select the cells you want to apply the conditional formatting on (In my case, B13:D17).Selecting the data
  2. Now, go to the New Formatting Rule window by following the Step-2 and Step-3 of Method-2. Select Use a formula to determine which cells to format rule type.
  3. Enter the condition/formula in the specified field (In my case, =AND($D13>=$C$4, $D13<=$C$6)) and select the desired format by following the Step-6 of Method-2 and click OK.Entering the formula

Explanation: The dollar sign ($) is known as the Absolute Symbol. It makes the cell references absolute and doesn’t allow any changes. You can lock a cell by selecting the cell and pressing the F4 button.

Here, =AND($D13>=$C$4, $D13<=$C$6) this formula 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).

It will format the selected cells according to the condition and selected format.

➥ Related: Excel Formula to Change Cell Color Based on Date

Conclusion

Conditional formatting in Microsoft Excel is an extremely useful feature. In this article, I’ve used this functionality to focus on four common methods of formatting date type values. I hope you were able to find a solution to your problem. Please leave a comment if you have any suggestions or questions. Thank you.


Further Readings

Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo