Excel/Date Color change


QUESTION: Excel 2010 - 2013

is there a way to format a date in a cell if this date is less than 30 days away from “TODAY” it would turn red.

thank you

ANSWER: Shane,

You can use conditional formatting.  Say the cell is C3.  then you would select the cell C3 and select conditional formatting from the Home tab

Select "new rule..." in the resulting dialog (toward the bottom of the dialog)

then choose:
"use a formula to determine which cells to format"

In the resulting dialog you will have a textbox labeled
"format values where this formula is true"

Now I assume that today() is moving toward your date in C3 so when Today gets closer than 30 days different but not past your date the cell will turn red.

so in that textbox put the formula


[note, you said less than so I used <30; it will remain red when today() equals the date and when today goes past the date it will not be red]

then click the format button, choose fill and select red

then OK your way out

I tested this and it worked as I expected.  

Again, I assumed the date in question is later than today() and as time passes, today() will approach that date.

Tom Ogilvy

---------- FOLLOW-UP ----------

Training Tracking
Training Tracking  
QUESTION: thanks for the help but I cant seem to get it to work?
I have a column (B3 -B100) that contain dates.
When these dates become <30 days away from today, I would like them
flagged red. So I can let them know their training is coming due.
Like a 30 day notice.

I tried your formula:
=And($C$3-today()<30,Today()<=$C$3) and replaced it to read


Then I added the "red" formatting.
and it does not work?

any ideas?

I would like all dates in column B to work. that is why I took out the
$ signs in formula.

Also if the dates are red .... can an email be automatically sent to their respective manager? (columns D and E in attached image)


You can send me worksheet with just your dates in the proper cells and I will get it working for you.  

Send to twogilvy@msn.com

Indicate which cells should be red now (today()).  

Sending an email would be pretty complex.  Everyday for 30 days, those cells are going to be red.  But yes, you could have such code.  You can find sample code for mailing from excel at Ron de Bruin's site:  http://www.rondebruin.nl/win/s1/outlook/mail.htm

from all those examples,  you should be able to set up what you want (I will leave you to implement you solution since you know what you want to do and where the information is).  

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 About.com. All rights reserved.