Conditional Formatting Dates Older Than Today in Excel

While handling data, including dates, it is often necessary to count, format, or highlight dates under various conditions. You can easily manage and format dates by using conditions in Excel. Follow this article to learn Conditional Formatting Dates that are Older Than Today in Excel.

📁 Download Excel File

Download this Excel file that we used to create this article so that you can practice.

Learn to Implement Conditional Formatting in Excel on Dates That Are Older Than Today with These 3 Unique Examples

Suppose a company’s server has data regarding the joining dates of present and upcoming employees. Your task is to arrange these under varying conditions. We present you with three unique ways to do so.

Here, we will use a specified time range ten days older than today to show conditional formatting on dates in Excel. The procedure is discussed here.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select range of cells that you want to highlight. Here, we have chosen the joining dates range which is cell C6:C15.
• Afterward, go to the Home option from the ribbon and select Conditional Formatting option from Styles group.

• A drop-down menu will appear; from there, select the New Rule.

• A small window will open named New Formatting Rule. From there, you will see Select a Rule Type, and among those, click on Use a formula to determine which cells to format.

• In the Edit the Rule Description box, enter the rule using an equal First, insert the cell number to highlight, and this is C5 here, followed by a “<” sign, and then insert the function “TODAY().”Then type “-10“. Finally, click Format command on the right side of the window.

The formula that we entered is –

• Another window will open titled Format Cells. Select the Fill option and choose a color to your wish. Then select OK.

• Finally, you can see the color you chose from the Sample.

• Select OK on both windows.

Now, you can see that the dates ten days older than today are highlighted with your chosen color.

You can also do it in a more simplified manner. However, we have discussed it below in a step-by-step way.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cells from where you want to format dates. For example, we have chosen cells C6:C15.
• Then, from Home tab, select Conditional Formatting, and from there, select the new rule.

• A new tab will show up, named New Formatting Rule, and from there, select Format only cells that contain.

• Then, from Edit the Rule Description, select the less than from the drop-down menu in the second box, and in the third box, type the function TODAY in the following way, then click on format command.

The formula that we have used is given here.

• A dialog box will open named Format Cells. Select the Fill option from there and choose a color.

You can see the color you chose from the Sample box.

• Select OK for closing the dialog boxes.

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

You can also apply conditional formatting in order to highlight the date between today and 30 days ago. Here is how you are going to do this:

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select range of data where the dates are assigned. In this example, this is going to be cell C6:C15.
• Now follow the sequence and click on these options serially. First, go to the Home tab, where you will find Conditional Formatting in Styles group, click on it, and from the dropdown menu, select  New Rules.

• A dropbox called New Formatting Rule will pop up on your screen.
• From Select a Rule Type, go for Format only cells that contain.

• In third blank box of the window, write down a formula using the TODAY function.

The formula we have written on the box is below.

• Again on the fourth box, type this formula. Notice that the “-30” is used to subtract 30 days from TODAY. Click Format box afterward.

• Again a window will open named Format Cells.
• From there, click on Fill tab and choose any color to highlight dates. Click OK in order to apply this.

• Now click OK on New Formatting Rules.

• See the highlighted dates that are in between 30 days from today.

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

How to Use Conditional Formatting on Dates Within 60 Days from Today in Excel

If you have followed the steps mentioned above rigorously, then finding dates within 60 days from today will be fine. Just follow these simple steps.

⬇️⬇️ STEPS ⬇️⬇️

• As mentioned above, you must select the cells first to highlight the dates within that cells. For my dataset, I have selected C6:C15.

• Now follow the sequence and click on these options serially. First, go to the Home tab, where you will find Conditional Formatting in Styles group, click on it, and from the dropdown menu, select Highlight Cells Rules, and from the options of it, select Between.
• You will find a small window named Between.

• From first box, delete default date and enter a formula using TODAY function.

The formula we have used is

• In the second box, remove the given date and type the following function: “-60” means 60 days or two months ago than today. So we are subtracting 60 days from today’s date. As you haven’t changed the default color from the third box, the highlighted cells will be red in color as the default mentioned.

The formula that we have used is given below.

• Finally, click OK for closing window. Notice that the days that are older than today but within 60 days from today are highlighted.

📕 Read More: 4 Ways to Use Conditional Formatting for Dates Overdue in Excel

How to Perform Conditional Formatting on Days Within 1 Year from This Day in Excel

Now we will demonstrate how to perform conditional formatting for days within 1 year from today’s day. So the dates that are only within 1 year of today’s date will be highlighted. Here are the steps you have to follow.

⬇️⬇️ STEPS ⬇️⬇️

• Select the ranges where your dates are stored. Here in our data, this range is cell C6:C15.
• Go to the Home option, and from there, click on Conditional Formatting from Styles option, click on the Highlight Cells Rules and then Click on Between.

• A window titled Between will open where you have to change both the default dates in boxes 1 and 2. Type this formula in box 1 to grab today’s date.

And in the second box, type the following formula to subtract 365 days or one year from today’s date.

• Finally, click OK, and you will see those highlighted days that are within the 1-year range from today.

📄 Important Notes

🖊️  We have used Microsoft 365 Version to develop our dataset. If you are using different versions of Excel, some options may vary.

🖊️  Customize the color of the highlighted cells as you wish.

📝 Takeaway from This Article

📌  You have learned how to do Conditional Formatting using different options of Conditional Formatting.

📌  Now, you can easily highlight any ranges of data older than today by using or modifying those steps mentioned in this article.

Conclusion

We have shown three possible examples of conditional formatting on dates that are older than today in Excel. I hope you find the approaches illustrated in this article helpful and easy to use. Please leave a comment with any suggestions or questions. Also, visit our Excelden page to enhance your Excel-related knowledge.

Related Articles

• Excel VBA: If Font Color Is Red Then Return Specific Output
• 6 Easy Ways to Highlight Lowest Value in Excel
• 5 Easy Ways to Highlight Highest Value in Excel