Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Conditional Formatting Today And Older

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello, I rarely use excel and i am organizing a large list with expiration dates.
What i am trying to do is have the dates that are today and older turn a certain color, so that they stand out among the others. I know I need to use conditional formatting but i don't know where to start with the formula.

I looked on the microsoft website and a couple other forums but they were no help.

Thanks a lot!

View Answers     

Similar Excel Tutorials

Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
Automatically Shade Every Other Row When You Add Data in Excel
How to make Excel automatically add row shading to every other row when you add data to the spreadsheet. This does ...
Shade Every Other Row in Excel Quickly
How to shade every other row in Excel quickly without using the Table feature. This method is for when you simply w ...
Display Comments (without hunting for them) - Excel 2003
To find options, go to the File menu, then click options, then click advanced and click on comments and indications ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Format Cells as a Fraction in Excel Number Formatting
- This free Excel macro will automatically format a selected cell or many selected cells in the Fraction number format in
Format Cells in The General (default) Format in Excel Number Formatting
- This free Excel macro formats a selected cell or range of cells to the General number format in Excel. This is the defa

Similar Topics

I have a spreadsheet that tracks publication dates and review dates. I would like the publication date to change color based upon its relationship with todays date. If the publication date is equal to or older than two years from today turn red, if it is equal to or older than a year and a half from today turn yellow. This seems like a pretty simple formula but I have tried several times and I can't seem to get it to work. Thanks for your help.

I am trying to highlight rows that have specific dates in excel using conditional formatting. I was able to get it to work using the formula below. However, I now need to pick a specific date to compare the rest of the worksheet so that if the report is opened 1 month from now the highlighting does not change. For example, I want dates that are older than 1 week from today to be highlighted yellow and dates older than 2 weeks to be highlighted red but if I open this report one month or one year from now I want all the formatting to be based on today's data (june 8, 2013) not the date on which the report is opened (Therefore I cannot user TODAY()).

Old formula:

New formulas I have tried:

I then entered today's date into another cell (THIS is the preferred method) into the spreadsheet (B1) and tried

None of these worked. Any help would be appreciated.

I would be grateful for any help on the following:

I have spreadsheet which contains several dates for different reasons.

I am trying to the change the colour of the dates (repeated down columns) if the dates have expired, the dates are older than 12 months or older than 3 months.

I have managed to use the formula in conditional formatting "less than =TODAY()-365."

Would be grateful for any help of a formula using 1 year instead of 365 days and also for 3 months

Many Thanks

I have 898 rows of information spread across 13 columns. Column "G" has
dates (898 *different* dates...). What I want to do is set a conditional
formula to change the font, of the whole row, only if the date is older than
"today". I must be able to select all of the cells and set the formatting at
once, but I can't seem to get it correct. Thanks.

I would like to use conditional formatting to highlight this week and the next 2 (3 weeks total with 3 different colors) Week should start on Monday and End on Sunday. Column C is how I would like it to work.
Right now I'm doing it like in column B, using today thru today +7 then for the next color today +7 thru today +14.... which isn't working.


* A B C D 1 Day Current how I want it Conditional Formatting for column B 2 Friday 5/22/2009 5/22/2009 =AND($B2>=TODAY(),$B2=TODAY()+8,$B2=TODAY()+15,$B2> Excel Jeanie HTML 4

Thank You
Matt O.

Hello I am fairly new to Excel 2007 conditional formatting.

I have a Health and Safety database with dates of certification. Some of these certifications expire 1 year after completion, and some expire 3 years after completion.

What I would like to do is place conditional formatting in them to change the font to orange when they are to expire in 45 days. And then to change to red when they expire.

So this is what i had in the conditional formatting field, for the 1 year certifications:

=Today()-320, and i had the font color format as orange
=Today()-365, and the font color format as red.

but it doesn't seem to be working, I don't know which of these need to be "on top" and which needs to be on bottom? or if these are even the right codes to get me where i need to be. Please help! thank you!

I have attached my "test" file. I really appreciate it. oh btw I deleted my formatting, as it was a mess .


I have a list of expiration dates that i need to flag according to several criteria.

I'd like to use conditional formatting to highlight each of these a different color:
Dates that expire in two months
Dates that expire in three months
Dates that have already expired

I have the last one down but i do not know what formula to input for two or three months in advance.

Any help would be greatly appreciated, thanks.

I do not think what I am asking is that unique but I cannot seem to find the right set up that works just like this already listed in the forums. Basically, I am using Excel 2007 and I have a sheet that shows the due dates for upcoming projects. Let's say the dates range from 2 weeks ago to a few months in the future. I want the dates that are today or before to be red (showing overdues), the dates within the next 14 days to be yellow (need attention), and any dates over 14 days in the future to be green. How do I use conditional formatting to achieve this? I tried several different ways but it seems like the overlap makes everything green or everything red. I am familiar with the basic idea of conditional formatting.

I have a workbook with employees' driving document details i.e. the dates their insurance and MoT (GB)! EXPIRES. At the moment I just sort the columns to see whose have run out.

I would like the cells to change when the insurance and MoT have expired i.e. if employee X's insurance ran out on 19.11.13 when I go into that sheet today 20.11.13, I need that cell to be highlighted, plus any others that may have expired.

So cells within columns B&C need to be highlighted if the date is 1 or more day less/older than today's date.

Does that make sense - can anyone help?

Many thanks

I have read numerous forums on conditional formatting using dates, but the more I read, the more I seem to get confused. Maybe it's because I'm using Excel 2007 and many forums talk to the 2003 version?

Here is my situation:

I have dates in cells. They are dates that documents were executed in the past.

These documents need to be updated/re-executed on an annual basis. So, at a minimum, one year from the date in cell, they should be re-executed.

I want to have the cell turn one color (yellow) when TODAY is <60 days before the annual anniversary (date in cell + 305).

I also want the cell to turn another color (orange) when TODAY is <30 days before the annnual anniversary (date in cell + 330)

Last. I want the cell to turn a third color (red) when the annual anniversary (date in cell +365) is < TODAY.

Can someone please help me?



I have a row that contains 2 date fields, say A2 and A3. These can be considered start and end dates. When TODAY() is between the start and end dates, I would like to change the color of the entire row. How would this be done using conditional formating?

Thanks in advance.


Hi all

I'm having a conditional formatting problem I'm hoping someone can help.

I have a range that contains dates, with some cells within containing text (e.g. "TBC" or "planning will occur wk42"). I have basic rules as below
- highlight red if older than today
- highlight green if today or in the future

The problem I have is that the cells that have text are highlighted green which I don't want. I also don't want to have to go through the range removing conditional formatting from each of those cells each time I copy over new data. Is there a way of telling excel to not use conditional formatting on those cells that contain text so I can manually highlight as I need? I can't help that some have text as that is how I receive the data.

Thank you!

I'm currently using the conditional formatting options for dates to highlight a date with a specific color if it references the following:

tomorrow = yellow
today = green
yesterday = red

When you choose the default options in Excel it doesn't actually spell out the formula to achieve these results. I'm asking for this hidden formula in case i wanted to eventually color a cell 2 days before or after today down the road sometime which Excel does not provide by default. Any help is greatly appreciated.

thank you!

Hi All,

I need some help with conditional formatting for dates.

Heres my criteria for conditional formatting:
1. if date is within 60 days - green color shading
2. if date > 60 days from today but < 120 days - yellow color shading
3. if date > 120 days from today - red color shading

seems simple enough but i have trouble figuring it out.. heres what i have to begin with & im sure I got it wrong.. help!


I have dates of items that were accomplished and would like to color code them to quickly see how long ago these items were accomplished...

Green for current to 9 months old
Yellow for 9 months old to 1 year old
Red for older then one year

The cell I wish to format contains dates so for example
Today is 7 Apr 2011
5 Apr 2011 would show up Green
20 Apr 2010 would show up yellow
1 apr 2010 would show up red.

The only problem I am having is I want them color coded from TODAYs date!

I have tried variations of -

Cell value is less then TODAY-270
Cell value is between Today-270 and Today-365
Cell value is greater then Today-365

Any help is appreciated...

thanks for any help you can provide.

I'm looking for a conditional formatting formula to keep track of follow-on
contract due dates. I've found many helpful formulas for calculating due
dates from a starting date...but, not working backwards from an end date.
Any ideas?

Some contracts are on a fiscal year schedule (30 Sep) and some are
off-cycle (any date.) How do I set up a formula that will color a cell red
one month before contract expiration and turn the cell yellow 180 days before
contract expiration. The formula must take into account that the cell
includes the expiration date.

Thanks in advance!

I have a spreadsheet I'm trying to make which will have various cells change color based upon the date entered compared to today's date. Here are the details of it, and what I've tried to do so far:

Various safety tickets that employees acquire have a 3 year expiry date on them. What I want to do is be able to enter in the issue date, and then have the conditional formatting color the cell that contains the date based on the following criteria:

Green = More than 6 months remaining
Yellow = Between 6 months and 30 days remaining
Red = 30 days until expiry

Black = Expired (I wanted to eventually add this one, but Excel 2003 was only allowing me to add 3 conditional formatting conditions, so I just went with the 3 colors. I'm getting Excel 2007 later today, so I might be able to add more with that)

Now here is what I have so far:

3 years = 1095 days, 2.5 years = 913 days, 2 years and 11 months = 1065 days

N3 = 913
N4 = 1065

Conditional Formatting on cell C21

Condition 1 - color Green


Condition 2 - color Red


Condition 3 - color Yellow


Ok, now I think this should work properly, but it doesn't. The ranges of the dates don't work as they should, and also, there are some "gaps" in which the cell ends up white. Anyone able to help me figure this out, or maybe a better way to do it?



I hope you can help me; I want to do a conditional formatting using dates; basically what I need is the date to turn red if it's older than 2 days of received:

i.e I receive a document on the 5/12/2010; and it's out by the 9/12/2010 then this latter date should turn red. I have a turnaround of 48 hrs.

So I have 5/12/2010 in C5 AND 9/12/2010 IN D5 and I want to apply the conditional formatting to D5 I hope I made some sense;

Thanks for your help

I have about 5 different dates in a row, and i would like it to change A1 a different color if one or all of those dates are within 90 days of today.

Is that even possible in the conditional formatting? or would i have to make a macro?

Hope that makes sense.

I have a column that contains dates. I need to turn the date yellow if it is within 90 days of today and red if it is past today. I have been working with conditional formatting and I can do either one, but I can't seem to get both to work at the same time.


I thought this would be simple but i just can't figure this out. Using Excel (not VB code) I want to change a cell color to red if "today" is older than 03/31/2007. The idea is to highlight the cell red if the first quarter of year is past (i.e. - 03/31/2007). I'm not comparing the cell content - the cell content is "Q1" and I want to turn background color RED if Q1 is past.

I've tried conditional formatting using "Formula": =today() > 03/31/2007 and it highlighted the cell RED, but when i change my formula to 12/31/2007 it still highlights it RED.

Is there another way to do this? Is my formula wrong?? Is my approach wrong??


I have a row of sequential daily dates. I want the cell containing today's date (today(), i.e. it should update dynamically) to be coloured in.

How do I do this? I can't figure out the formula to enter in the conditional formatting.

(Excel 2007)

Thank you.

Hello all. I would like to apply conditional formatting to a date cell as follows. If it is today or before today (red background); if it is after today but within 30 days (orange) or if it is after 30 days (green). Any tips on the formula would be appreciated.

what i am trying to do is to have the conditional formatting highlight anything that is older than what ever month it currently is so like if i was to set the rule to =TODAY()-30 it would still include a couple of days of this month i want it to never include anything that is in the month that we are in.


I am creating a condition format in my spreadsheet that will cause font in a range (d10:i10) to turn red based on two conditions: if a certain cell is left blank (I10) and if the date in one cell (E10) is older than 30 days from today. Here is what I have but it's not working, I get an error message...anyone know why?