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.
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)
"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.
---------- FOLLOW-UP ----------
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?
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 email@example.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).