Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

List all Conditional Formatting Formulas in Excel
List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...
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 ...
Get the Current Date in Excel
How to get the current date using Excel.  This method updates the date every day so that it is always accurate. To ...
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 ...

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


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

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!





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.


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.


Hi to all,

I want to ask for help to set up an excel sheet with a formula. The scenario is like this:

We need to track the batteries if it is expired or not.Expiration is 3 months. once you received the battery, you need to put the date in the sheet (A1) and then the expiration will be automatically calculated in B1. another also, in B1, will change its color according to how may days left before the expiration date.

black color if the expiration is < 15 days
red color if the expiration is 45 days
white color if B1 is blank

If the expiration is

My boss wants to color code dates based on certain date ranges and have them update automatically when the document is opened. (This is to show whether an employee's qualification is current, due to expire within 6 months, due to expire within 1 month, and expired.) Is there any way to do this? I've been experimenting with the Conditional Formatting in the Format menu, but I haven't been successful.

The criteria I've been using is:

(Within 6 mos)
Condition 1: Cell value is between ="Today()+31" and ="Today()+180"

(Within 1 mo)
Condition 2: Cell value is between ="Today()"+1 and ="Today()+30"

(Expired)
Condition 3: Cell value is less than or equal to ="Today()"


The dates listed in each cell are in the following format: 8/1/2008

All the cells are showing as expired.

Is there another way to do this or am I entering the formulas or dates incorrectly?

Thank you!


Hello - I am trying to write a formula to return the next date after today, but am having some trouble with it. Here is my scenario:

In cell K25 I have a value where if it is greater than 49 I want the forumla to look at the values of Cells L3:L22 (these cells have a range of dates in them). I want the formula to go through these cells and pick the next date after today....If the value in Cell K25 is Less than or equal to 49 I want it to just return Todays date.

I found this post http://www.excelforum.com/excel-gene...ter-today.html which was basically what I was looking for, but for some reason when I put that into my formula it will return the smallest date, even if it is older than today.

So going back to my formula....Here is what I have currently in my cell:
=IF(K25>49,MIN(IF(L3:L22>TODAY(),L3:L22)),TODAY())

Like I mentioned above - this results with dates that are older than today - what can I do to make it return results that are only after todays date?

Thanks for any help you can provide!


I've read a few posts and am confused as to how to get some conditional formatting to work with dates. Here is my situation:

I have a list of dates in different columns and rows and would like to have the cell color and/or type color to change based on the date within the cell. two sets of conditions:

Set 1
When today is the same day as the date in the cell, it does not change
When the date is one day old, the cell changes to a color
When the date is 7 days old, the cell changes to a different color.

Set 2
When today is the same day as the date in the cell or 1-4 days old, it does not change
When the date is five days old, the cell changes to a color
When the date is 14 days old, the cell changes to a different color.

Hi, I am trying to use the conditional formatting in Excel 2003. My spreadsheet has dates of when assessments have been undertaken, these need to be reviewed annually so I wish to have an early warning system by making the cells turn a different colour when it reaches a certain period.

Red - if the date is over 350 days from todays date
Orange - if the date is 334 days from todays date
Green - if the date is more than 300 days from todays date

I have tried to use this formula but it doesn't appear to have the results I thought I would:

=AND(INT(D4)>TODAY(),(INT(D4)-TODAY())>350)

Similar ones for the others too.

Can anyone shed any light on this?

I would also like to make another cell (A4) turn red if there are any other red cells in that row - what formula do I need for this? I have tried different alternatives but it doesn't appear to work.

Thanks for any help in advance


I have a column of dates ranging from blank to future dates. I want to apply conditional formatting based on comparison to today's date. I'm working in Office 2007.

Using the stoplight icons with the checkmark, exclamation point, and ex symbols, I want the following results:

Green: value >= (today's date + 2 months)
Yellow: value > today's date < (today's date + 2 months)
Red: value

Hi Guys,
If i have a list of dates in the past eg 22-06-08, 29-07-08 & 19-08-08 in A1,A2 & A3 resp. I would like to create conditional formatting so that 30 days before Today the 22-06-08 cell would stay green, the 29-07-08 cell would turn amber and the 19-08-08 cell would turn red. Will the colouring over right the cells ie will the amber cell turn red at the right timing? Couldn't quite find the fix googling. Any ideas please??? THANKS a million!


Hopefully this will be quick.

I have dates in column N and notes in column O.
I want to change the color in column O if the date in column N is equal to or older than today.
For instance if N2 is 9/1/06 then O2 should be red. Same with N3 and O3, etc.

I've tried "Format only cells that contain" "cell value" "less than or equal to" =N2:N300=TODAY() and applied it to $O$O and everything in O turns red. I've also applied ti to $O#2:$O$300 and the same thing happens.

Anyone have ideas?


OK - I dont know that I was very clear in my previous thread...

I have attached a version of the worksheet that I use to clarify.

Basically what I need is for all dates between 14 days and 21 days ago to turn Orange.

And everything older than 21 days to go Red.

Everything else needs to stay the same - only that 1 column needs to change colours. I have found ways of making all 'last months' a different colour but not able to make everything older that 21 days Red.

Anyway any help would be great.

thanks


Hello, I have an excel sheet with a list of dates, and what I'd like to know is how I can colour/highlight the dates more than four years ago, preferably to the month. I've tried to use conditional formatting to do this but failed, so any help would be greatly appreciated.


Is it possible to have a formula so that Excel will Change font color when an entered date is 3 years older than today? I need it to calculate from the present date.


I am using conditional formatting to turn the date green when in the current month if no Invoice number is recorded in another column. The second condition turns it red if still no invoice number and the date is older than the current month. These are my two formulae:

=AND($AD1="",IF(MONTH($AH1)=MONTH(TODAY()),1,0))
=AND(($AD1)="",ISNUMBER($AH1),OR(YEAR($AH1) < YEAR(TODAY()),MONTH($AH1) < MONTH(TODAY())))

Unfortunately I still cannot figure a way to get Excel to recognise that if the year is in the future it should not format. I have had several helpful suggestions but I can't get my head around how to build them into the formulae. The biggest problem is that I can't base the green one on a specific date because it spans a whole month. Please can any of you Excel gurus help me out?


Ok... you all are probably going to think I'm crazy....

What I'm trying to do is set up a nested IF, NOT, AND statement with conditional cell formatting. I already used the Menu cell formatting for a different purpose and because I can't put in more than three conditions, I need to manually put in the conditional formatting with a formula.

In Layman's terms I'm trying to make it say the following:

IF Cell A1 does NOT equal "No" AND the DATEDIF between A2 and TODAY > 30 days, then Color row A blue.

This is what I have for the formula so far:
=IF(W2=(NOT(W2="No"))AND((DATEDIF (R2, TODAY)>30)),=COLOR A2:AZ2=(155,10,0))