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).

Follow these steps to apply this solution:

1. Select the cells you want to apply the conditional formatting on (In my case, B7:D11).
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.
4. A new window named A Date Occurring should appear. Select the In the last 7 days option from the first drop-down menu.
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).

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.

Follow these steps to apply this solution:

1. Select the cells you want to apply the conditional formatting on (In my case, B9:C19).
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.
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) and select the Format option.

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

6. A new window named Format Cells should appear. Select your desired format and click OK (In my case, Font Style: Bold, Fill: 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.

Follow these steps to apply this solution:

1. Select the cells you want to apply the conditional formatting on (In my case, B11:L15).
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.

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.

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.

Follow these steps to apply this solution:

1. Select the cells you want to apply the conditional formatting on (In my case, B13:D17).
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) and select the desired format by following the Step-6 of Method-2 and click OK.

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

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.

The post How to Use Conditional Formatting in Excel Based on Dates appeared first on ExcelDemy.