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 Video Tutorials

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

Excel 2000 ... What I have

Col T ... contains values ... 0 & 1
Col AA ... contains dates ... mm/dd/yy

In Col AR I wish Conditional Formatting Formula for when
condition is:

Col T = 1
Col AA = date older than 3 years from Today.

Thanks ... Kha


I have a table that has expiration dates for certain products, what I would like to do is set up conditional formatting in the Report where the date shows up in red if the expiration date is within 30 days from expiring. I've searched but only can find what I'm looking for in Excel, ie.


But that doesn't work in Access.

Is this possible?


In the attachment I'm trying to use conditional formating to express dats that or older then 300 days for today to turn red and the ones that are not older to turn green. Any Idea? Thanks you!


I am trying to get the red /yellow/ green stop lights to change colors but have not figured out how to yet so I thought I would ask for some help here.

I am in shipping and I have a backorder log in an excell spreadsheet. The dates are in the A column. What I want is for any dates older than three days from the current date to go yellow, and any dates older than 5 days from the current date to go red. Green should be for any dates up to the yellow status.

I have tried:
Icon Value Type
Red >= =today()+7 Formula
yellow >= =today()+3 Formula

Any help would be greatly appreciated.

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'm lost.

I have a column which holds dates. I'm simply trying to apply a conditional format of IF Cell Value is Less Than or Equal To "=TODAY()" then the text is red.

Problem lies in that it turns all the dates red, no matter what.

Does "=TODAY()" not work as todays date in conditional formatting?
If not, how could I achieve the desired effect of durning dates red if that date is today or has passed.

Thanks in advance


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.


I have a conditional formatting warning on some cells so that if the date in
B3 is older than today the background goes red

Formula is = (TODAY()-B3)>0

How would I write this so that the action is to go red unless there is a *
on cell B10 ?



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!

First, if this is the incorrect forum, please move.

I have (attached) an Excel 2007 sheet used to track training. It's pretty simple and straight forward. Most fields use dates (formatted as Mmm-yy). Some are either 'QUAL' or 'UNQUAL'. All fields (except names and training titles) are formatted as 'Date'. I currently have all my needs met through conditional formatting as in the the 'CURRENT' tab.

Here are the conditions in order:
If blank, then fill White
If "UNQUAL", then fill Yellow
If 2 months or older [=TODAY()-60], fill Red/font White/Bold
If less than today [=TODAY()], fill Pink/font Red/Bold

When training is complied with, I insert a comment stating so, so I can have tracking on my people. (There's a log in the process between completing the training and the training monitor updating the "system".)

I can't have the comments showing 'cuz there'd be too many. W/ the 60-days-or-older training, you can't see the red comment triangle. I can easily change the color w/ no problems.

But...initially I tried to change the fill effects (horizontal shading), and just by changing the format the conditional format got screwy.

In the tab ChangedFill, you can see that by simply changing the formatting, the blank fields are filled in w/ the formatting requried for the TODAY()-60....It makes no sense how a format change could affect how the conditional formatting is applied.

It must be something in the Excel Twilight Zone....

Any ideas on why this happens?

C2 Cell formatted for Date

Currently is shows red if its older than today showing something has expired using: Cell Value is less than =TODAY() and shows Green if it still OK using greater to or equal to =TODAY()

But i want:

Red is older than today
Amber is today or within 7 days
Green if date is over 7 days

Ive tried using 'between' but keep getting white cells..... Thanks

Hi all,

I wonder if someone can help with conditional formatting in Excel 2007 please.

I have a workbook with multiple sheets. The first sheet has a column with dates that are copied from a cell in other sheets in the same workbook. The date cells in this column simply copy dates from the other sheet with a formula =BWCL!D2 The cells are formatted as a date.

I simply want to apply a conditional format to highlight any cell that shows a date that is older than a month. I've been trying to use a formula in the conditional format that is G3<(TODAY-30) or even =IF(G3<(TODAY-30)) .
I've also tried doing the logic in another cell which works fine and just using for e.g IF(G3<H3) in the conditional format.

Any ideas please and 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!