Tom’s Tutorials For Excel: Formatting Dates as Past or Future
Here’s how you can mark dates in the past and/or in the future, based on some time interval such as less than or more than 30 days from today’s date. For example, in the first picture, today’s date is held in cell E2 using the TODAY function. Conditional Formatting in column C colors dates more than 30 days ago as blue, and more than 30 days from today as yellow.
To do this, select the cells of interest, in this case range C5:C15. Then, press Alt+O+D on your keyboard to show the Conditional Formatting dialog box.
If you are using Excel version 2003 or before, the Conditional Formatting dialog box looks like the next picture, where you can enter the two formula rules for past and future time.
The formula for dates more than 30 days ago is:
=AND(LEN(C5)>0,C5<$E$2-30)
The formula for dates more than 30 days in the future is:
=AND(LEN(C5)>0,C5>$E$2+30)
Two points about these formulas:
• The LEN function keeps empty cells uncolored, which Excel would interpret as a date.
• Cell E2 houses today's date and is an efficient reference for the Conditional Formatting formulas, rather than using the TODAY function in all the formatted cells' formula rules. Also, if you change your base time element, for example from today to yesterday, you only need to change the formula in cell E2 from =TODAY() to =TODAY()-1.
If you are using Excel version 2007, 2010, or 2013, you'll first see the Rules Manager dialog box. Click the New Rule button as indicated.
Go ahead and enter the same formulas as shown above for Excel versions up to 2003:
The formula for dates more than 30 days ago is:
=AND(LEN(C5)>0,C5<$E$2-30)
The formula for dates more than 30 days in the future is:
=AND(LEN(C5)>0,C5>$E$2+30)
But wait! What if you just want to use one color? Fair question!
You might not care about seeing a visual difference between 30+ days ago and 30+ days from now, but you would want to see the visual indication of dates that are either 30+ days past or present.
For this, you only need one color, which means means you only need one formula rule:
=AND(LEN(C5)>0,OR(C5<$E$2-30,C5>$E$2+30))
In Excel versions 2003 or before, the Conditional Formatting dialog box looks like this:
In Excel versions 2007 or after, the Conditional Formatting dialog box looks like this:
I have used your method in excel 2016 but a message appeared that saying i entered too many formula.. So i used quick analysis to do it.
These formulas in this example are very small so if you are getting a message that you have too many, you must really have a LOT of them in your workbook. Excel has a limitation on conditional formats in a single workbook so maybe you need to consolidate your conditions or figure out a different presentation design.