Excel Conditional Formatting for Dates within 30 Days

Conditional formatting has made it easy to highlight important cells or ranges of cells, emphasize unusual values, and visualize data, color scales, and icon sets that related to specific variations in the data. A conditional format changes the outlook of cells based on the conditions that we want. If the conditions return true, the cell range is formatted; if the conditions return false, the cell range is not formatted. It can be used for formatting dates too for any purpose. This article will guide you for conditional formatting dates within 30 days in Excel.


Download Practice Book

You can download the free Excel template from here and practice on your own.


3 Examples of Conditional Formatting for Dates within 30 Days in Excel

Let’s get introduced to our dataset first. I have placed some order IDs, their order dates, and amounts within 3 columns and 11 rows.

Case 1: Conditional Formatting for Dates within 30 Days for Dates in a Range

In our very first method, I’ll show how to use the Between option of Conditional Formatting to format dates within 30 days. You can set here any date range.

Step 1:

➥ Click as follows: Home > Conditional Formatting > Highlight Cells Rules > Between

A dialog box will open up.

Using Between Option in Excel

Step 2:

➥ Now set a date range between 30 days. I have set 30-11-21 to 31-12-21.

➥ Press OK then.

Now we have got our desired dates with highlighted colors.

Read more: Excel Conditional Formatting Based on Date Range


Case 2: Conditional Formatting for Dates within 30 Days for a Specific Date

Now we’ll use the A Date Occurring option for conditional formatting dates within 30 days. We won’t need to set dates here, There are some default options here like Last month/ This month/ Next month. We’ll use it directly. But here the limitation is, we can’t use it for the dates before last month or dates after next month.

Step 1:

➥ Press as follows: Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.

A dialog box will appear.

Using A Date Occurring Option in Excel

Step 2:

➥ Now select your desired month from the date selection bar.

➥ Then just press OK.

Using A Date Occurring Option in Excel

Here’s the output for Last month.

Using A Date Occurring Option in Excel

It’s the output for This month.

Using A Date Occurring Option in Excel

This last one is the output for Next month.

Read more: Apply Conditional Formatting to the Overdue Dates in Excel


Case 3: Combine AND and TODAY Functions with Conditional Formatting for Dates within 30 Days in Excel

In our last method, I’ll combine the AND function and the TODAY function for conditional formatting dates from today to the next 30 days. The  AND function returns TRUE if all conditions are TRUE and return FALSE if any of the conditions are FALSE. The TODAY function returns the current date.

Step 1:

➥ Click serially: Home > Conditional Formatting > New Rule.

A dialog box named “Edit Formatting Rule” will open up.

AND and TODAY Functions for Conditional Formatting Dates Within 30 Days

Step 2:

➥ Select Use a formula to determine which cells to format from Select a Rule Type bar.

➥ Then type the formula given below in Edit the Rule Description bar.

=AND(C5>=TODAY(), C5<=(TODAY()+30))

➥ Click Format.

Format Cells dialog box will appear.

AND and TODAY Functions for Conditional Formatting Dates Within 30 Days

Step 3:

➥ Now choose your desired color from the Fill option. I have chosen lite green.

Press OK and we’ll go back to the previous dialog box.

AND and TODAY Functions for Conditional Formatting Dates Within 30 Days

Step 4:

➥ Finally, just press OK.

AND and TODAY Functions for Conditional Formatting Dates Within 30 Days

Now you will observe that the dates from today to the next 30 days are highlighted with our chosen lite green color.

For dates from today to the previous 30 days just apply this formula-

=AND(C5<=TODAY(), C5>=(TODAY()-30))

👇 How does the formula work?

C5>=TODAY()

Here, the TODAY function will check if the date in Cell C5 is great than todays’ date or not. So it returns-

FALSE

C5<=(TODAY()+30)

Next, it will check if the date is less than or equal to today’s date + 30 days and returns-

TRUE

AND(C5<=TODAY(), C5>=(TODAY()-30))

Finally, the AND function will encapsulate these two conditions. When both of them are true, the date is highlighted, else it is not highlighted.


Conclusion

I hope all of the methods described above will be good enough for Excel conditional formatting dates within 30 days. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

2 Comments
  1. Case #3 was exactly what I needed!! Thanks.

    Instead of breaking it into two separate arguments, I was doing a range (i.e. – TODAY()<=C5<=(TODAY()-30)). Excel accepted it as a range, but it ultimately doesn't work (and was quite frustrating to figure out Excel was the problem and not the formula).

Leave a reply

ExcelDemy
Logo