Conditional Formatting Today And Older

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
green

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.

test

* 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
and
=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 .

Hello,

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'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

-tab

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?

Thanks
Mdecker

Hi,

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.

Rusty

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 ?

Thanks

--
Tobit

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?

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?

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 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!

=IF(A2-TODAY()

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
and
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

=$C21+$N$3>=TODAY()

Condition 2 - color Red

=$C21+$N$4<TODAY()

Condition 3 - color Yellow

=AND($C21+$N$3<TODAY(),$C21+$N$4>TODAY())


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?

Thanks!

Hi

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.

Hi all.

I need help please with conditional formatting, where a due date of a payment is entered in the cell and if it's overdue (i.e. older than today) it shows as red.

Thanks,

Paul