Excel conditional formatting dates within 90 days

For a list of date and you want to highlight the date cells that are older than 30 days since now, would you highlight them one by one manually? This tutorial introduces how to highlight dates older than 30 days with Conditional Formatting in Excel, and easily select and count dates older than a specific date with an amazing tool.

Table of Contents Show

How to calculate 30/60/90 days from today in Excel
How to get 30/60/90 days before today in Excel
How to calculate N business after/prior to today
Date and Time Wizard
Date Picker for Excel
How to highlight dates 30, 60 and 90 days from today
Practice workbook for download
You may also be interested in

Highlight dates older than 30 days with conditional formatting
Easily select and highlight dates older than a specific date with an amazing tool
More tutorials for highlighting cells...

Highlight dates older than 30 days with conditional formatting

With Excel’s Conditional Formatting function, you can quickly highlight dates older than 30 days. Please do as follows.

1. Select the dates data and click Home > Conditional Formatting > New Rule. See screenshot:

2. In the New Formatting Rule dialog, you need to:

2.1) Select Use a formula to determine which cells to format option in the Select a Rule Type section;
2.2) Enter the below formula into the Format values where this formula is true box;
=A2<TODAY()-30
2.3) Click the Format button and specify a fill color to highlight the date cells;
2.4) Click the OK button. See screenshot:

Note: in the above formula, A2 the first cell of your selected range, and 30 indicates older than 30 days, you can change them as your need.

Now all dates older than 30 day since today are highlighted with specified fill color.

If there are blank cells in the date list, they will be highlighted as well. If you don’t want the empty cells to be highlighted, please go ahead to create another rule.

3. Select the date list again, click Home > Conditional Formatting > Manage Rules.

4. In the Conditional Formatting Rules Manager dialog box, click the New Rule button.

5. In the opening Edit Formatting Rule dialog box, please:

5.1) Select Use a formula to determine which cells to format option in the Select a Rule Type section;
5.2) Enter the below formula into the Format values where this formula is true box;
=ISBLANK(A2)=TRUE
5.3) Click OK.

6. When it returns to the Conditional Formatting Rule Manager dialog box, you can see the rule is listed out, check the Stop if True box for it. And finally click the OK button.

Then you can see only dates older than 30 days in selected range are highlighted.

Easily highlight dates older than a specific date with an amazing tool

Here introduce a handy tool – Select Specific Cells utility of Kutools for Excel for you. With this utility, you can select all date cells in a certain range which is older than a specific date, and highlight it with background color manually as you need.

Before applying Kutools for Excel, please download and install it firstly.

1. Select the date cells, click Kutools > Select > Select Specific Cells.

2. In the Select Specific Cells dialog box, you need to:

2.1) Select Cell in the Selection type section;
2.2) Choose Less than from the Specific type drop-down list and enter a certain date you want to select all dates less than it into the text box;
2.3) Click the OK button.
2.4) Click OK in the next popping up dialog box. (This dialog box informs you how many cells match the condition and selected.)

3. After selecting the date cells, if you need to highlight them, please manually go to Home > Fill Color to specify a highlight color for them.

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

Relative Articles:

Conditional format dates less than/greater than today in Excel
This tutorial shows you how to use the TODAY function in conditional formatting to highlight due dates or future dates in Excel in details.

Ignore blank or zero cells in conditional formatting in Excel
Supposing you have a list of data with zero or blank cells, and you want to conditional format this list of data but ignore the blank or zero cells, what would you do? This article will do you a favor.

Copy conditional formatting rules to another worksheet/workbook
For example you have conditionally highlighted entire rows based on duplicate cells in the second column (Fruit Column), and colored the top 3 values in the fourth column (Amount Column) as below screenshot shown. And now you want to copy the conditional formatting rule from this range to another worksheet/workbook. This article comes up with two workarounds to help you.

Highlight cells based on length of text in Excel
Supposing you are working with a worksheet which has list of text strings, and now, you want to highlight all the cells that the length of the text is greater than 15. This article will talks about some methods for solving this task in Excel.

More tutorials for highlighting cells...

The tutorial shows how to create a date calculator in Excel exactly for your needs to find a date any N days from or before today, counting all days or only business days.

Are you looking to calculate the expiration date that is exactly 90 days from now? Or you wonder what date is 45 days after today? Or you need to know the date that occurred 60 days before today (counting only business days and all days)?

Whatever your task is, this tutorial will teach you how to make your own date calculator in Excel in under 5 minutes. If you don't have that much time, then you can use our online calculator to find the date that is the specified number of days after or prior to today.

Want a quick solution to "what is 90 days from today" or "what is 60 days before today"? Type the number of days in the corresponding cell, press Enter, and you will immediately have all the answers:

Need to calculate 30 days from a given date or determine 60 business days prior to a certain date? Then use this date calculator.

Curious to know what formulas are used to calculate your dates? You will find them all and a lot more in the following examples.

How to calculate 30/60/90 days from today in Excel

To find a date N days from now, use the TODAY function to return the current date and add the desired number of days to it.

To get a date that occurs exactly 30 days from today:
=TODAY()+30

To calculate 60 days from today:
=TODAY()+60

What date is 90 days from now? I guess you already know how to get it :)
=TODAY()+90

To make a generic today plus N days formula, input the number of days in some cell, say B3, and add that cell to the current date:

=TODAY()+B3

Now, your users can type any number in the referenced cell and the formula will recalculate accordingly. As an example, let's find a date that occurs 45 days from today:

How this formula works

In its internal representation, Excel stores dates as serial numbers beginning with January 1, 1900, which is the number 1. So, the formula simply adds the two numbers together, the integer representing today's date and the number of days you specify. The TODAY() function is volatile and automatically updates every time the worksheet is opened or recalculated - so when you open the workbook tomorrow, your formula will recalculate for the current day.

At the moment of writing, today's date is April 19, 2018, which is represented by the serial number 43209. To find a date, say, 100 days from now, you actually perform the following calculations:

=TODAY() + 100

= April 19, 2018 + 100

= 43209 + 100

= 43309

Convert the serial number 43209 to the Date format, and you'll get July 28, 2018, which is exactly 100 days after today.

How to get 30/60/90 days before today in Excel

To calculate N days before today, subtract the required number of days from the current date. For example:

90 days before today:
=TODAY()-90

60 days prior to today:
=TODAY()-60

45 days before today:
=TODAY()-45

Or, make a generic today minus N days formula based on a cell reference:

=TODAY()-B3

In the screenshot below, we calculate a date that occurred 30 days before today.

How to calculate N business after/prior to today

As you probably know, Microsoft Excel has a few functions to calculate working days based on a start date as well as between any two dates that you specify.

In the below examples, we will be using the WORKDAY function, which returns a date that occurs a given number of working days ahead of or prior to the start date, excluding weekends (Saturday and Sunday). If your weekends are different, then use the WORKDAY.INTL function that allows custom weekend parameters.

So, to find a date N business days from today, use this generic formula:

Here are a few examples:

10 business days from today
=WORKDAY(TODAY(), 10)

30 working days from now
=WORKDAY(TODAY(), 30)

5 business days from today
=WORKDAY(TODAY(), 5)

To get a date N business days before today, use this formula:

WORKDAY(TODAY(), -N days)

And here are a couple of real-life formulas:

90 business days prior to today
=WORKDAY(TODAY(), -90)

15 working days before today
=WORKDAY(TODAY(), -15)

To make your formula more flexible, replace the hardcoded number of days with a cell reference, say B3:

N business days from today:
=WORKDAY(TODAY(), B3)

N business days before today:
=WORKDAY(TODAY(), -B3)

In a similar manner, you can add or subtract weekdays to/from a given date, and your Excel date calculator can look like this.

Do you remember the Excel Online Date Calculator showcased in the very beginning of this tutorial? Now you know all the formulas and can easily replicate it in your worksheets. You can even craft something more elaborate because the desktop version of Excel provides far more capabilities.

To give you some ideas, let's design our Excel Date Calculator right now.

Overall, there can be 3 choices for calculating dates:

Based on today's date or specific date
From or before the specified date
Count all days or only working days

To provide all these options to our users, we add three Group Box controls (Developer tab > Insert > Form Controls > Group Box) and insert two radio buttons into each group box. Then, you link each group of buttons to a separate cell (right-click the button > Format Control > Control tab > Cell link), which you can hide later. In this example, the linked cells are D5, D9 and D14 (please see the screenshot below).

Optionally, you can enter the following formula in B6 to insert the current date if the Today's date button is selected. It is not actually necessary for our main date calculation formula, just a small courtesy to your users to remind them what date today is:

=IF($D$5=1, TODAY(), "")

Finally, insert the following formula in B18 that checks the value in each linked cell and calculates the date based on the user's choices:

=IF(AND($D$5=1, $D$9=1, $D$14=1), TODAY()+$B$3, IF(AND($D$5=1, $D$9=1, $D$14=2), WORKDAY(TODAY(),$B$3), IF(AND($D$5=1, $D$9=2, $D$14=1), TODAY()-$B$3, IF(AND($D$5=1, $D$9=2, $D$14=2), WORKDAY(TODAY(),-$B$3), IF(AND($D$5=2, $D$9=1, $D$14=1), $B$7+$B$3, IF(AND($D$5=2, $D$9=1, $D$14=2), WORKDAY($B$7, $B$3), IF(AND($D$5=2, $D$9=2, $D$14=1), $B$7-$B$3, IF(AND($D$5=2, $D$9=2, $D$14=2), WORKDAY($B$7,-$B$3), ""))))))))

It may look like a monstrous formula at first sight, but if you break it into individual IF statements, you will easily recognize the simple date calculation formulas we've discussed in the previous examples.

And now, you select the desired options, say, 60 days from now, and get the following result:

To have a closer look at the formula and probably reverse-engineer it for your needs, you are welcome to download our Date Calculator for Excel.

If you are looking for something more professional, you can quickly calculate 90, 60, 45, 30 days from now (or whatever number of days you need) with our Excel tools.

Date and Time Wizard

If you've had a chance to pay with our Date and Time Wizard at least once, you know that it can instantaneously add or subtract days, weeks, months or years (or any combination of these units) to a certain date as well as calculate the difference between two days. But did you know it can also calculate dates based on today?

As an example, let's find out what date is 120 days from today:

Enter the TODAY() formula in some cell, say B1.
Select the cell where you want to output the result, B2 in our case.
Click the Date & Time Wizard button on the Ablebits Tools tab.
On the Add tab, specify how many days you want to add to the source date (120 days in this example).
Click the Insert formula button.

That's it!

As shown in the screenshot above, the formula built by the wizard is different from all the formulas we've dealt with, but it works equally well :)

To get a date that occurred 120 days before today, switch to the Subtract tab, and configure the same parameters. Or, enter the number of days in another cell, and point the wizard to that cell:

As the result, you will get a universal formula that recalculates automatically every time you enter a new number of days in the referenced cell.

Date Picker for Excel

With our Excel Date Picker, you can not only insert valid dates in your worksheets in a click, but also calculate them!

Unlike the Date and Time Wizard, this tool inserts dates as static values, not formulas.

For example, here's how you can get a date 21 days from today:

Click the Date Piker button on the Ablebits Tools tab to enable a drop-down calendar in your Excel.
Right-click the cell where you'd like to insert the calculated date and choose Select Date from Calendar from the pop-up menu.
The drop-down calendar will show up in your worksheet with the current date highlighted in blue, and you click the calculator button in the upper right corner:
On the upper pane, click the Day unit and type the number of days to add, 21 in our case. By default, the calculator performs the addition operation (please notice the plus sign in the display pane). If you'd like to subtract days from today, then click the minus sign on the lower pane.
Finally, click
to show the calculated date in the calendar. Or, press the Enter key or click
to inset the date into a cell:

How to highlight dates 30, 60 and 90 days from today

When calculating expiration or due dates, you may want to make the results more visual by color-coding the dates depending on the number of days prior to expiration. This can be done with Excel Conditional Formatting.

As an example, let's make 4 conditional formatting rules based on these formulas:

Green: more than 90 days from now
=C2>TODAY()+90
Yellow: between 60 and 90 days from today
=C2>TODAY()+60
Amber: between 30 and 60 days from today
=C2>TODAY()+30
Red: less than 30 days from now
=C2<TODAY()+30

Where C2 is the topmost expiry date.

Here are the steps to create a formula-based rule:

Select all the cells with the expiry dates (B2:B10 in this example).
On the Home tab, in the Styles group, click Conditional Formatting > New Rule…
In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
In the Format values where this formula is true box, enter your formula.
Click Format…, switch to the Fill tab and select the desired color.
Click OK two times to close both windows.

Important note! For the color codes to apply correctly, the rules should be sorted exactly in this order: green, yellow, amber, red:

If you don't want to bother about the rules order, use the following formulas that define each condition exactly, and arrange the rules as you please:

Green: over 90 days from now:
=C2>TODAY()+90

Yellow: between 60 and 90 days from today:
=AND(C2>=TODAY()+60, C2<=TODAY()+90)

Amber: between 30 and 60 days from today:
=AND(C2>=TODAY()+30, C2<TODAY()+60)

Red: less than 30 days from today:
=C2<TODAY()+30

Tip. To include or exclude the boundary values from a certain rule, use the less than (<), less than or equal to (<=), greater than (>), greater than or equal to (<=) operators as you see fit.

In a similar manner, you can highlight past dates that occurred 30, 60 or 90 days ago from today.

Red: more than 90 days before today:
=B2<TODAY()-90
Amber: between 90 and 60 days before today:
=AND(B2>=TODAY()-90, B2<=TODAY()-60)
Yellow: between 60 and 30 days before today:
=AND(B2>TODAY()-60, B2<=TODAY()-30)
Green: less than 30 days before today:
=B2>TODAY()-30

More examples of conditional formatting for dates can be found here: How to conditionally format dates and time in Excel.

That's how you calculate dates that are 90, 60, 30 or n days from/before today in Excel. To have a close look at the formulas and conditional formatting rules discussed in this tutorial, I invite you to download our sample workbook below. Thank you for reading and hope to see you on our blog next week!