Let’s say I have an Excel spreadsheet that has a list of team member names and their last training date certifications. I would like to make it easy to determine who has a certification that is over one year old. I can use Excel conditional formatting to make the date turn red if the date is over a year old and that person need to become re-certified. Ok, first let’s look at the screen shot in which I have a simple list of names in column A and dates in column B. My first date of my list begins in B2.

I start out by clicking on my first date cell. Then I click the Conditional Formatting command button–>New Rule.

I then click “Use a formula to determine which cells to format.” If I start out by clicking my mouse on my first date B2 it will automatically write =$B$2. I need to modify the absolute referencing to make it just say =B2 since I want to copy the formula down. My final formula reads =B2<=TODAY()-365. The TODAY() function always returns today’s date. The 365 is for subtracting a year ago to compare if the date is older than 1 year. I then click Format to choose red fill color. After I click OK I can verify if it works.

Once the first cell has the date conditional formatting, I then need to use the format painter to copy and paste the format to the remaining date cells. I also could copy…paste special the B2 cell onto the other cells choosing the Formats only choice.

My final result ends up with this. 🙂

I have red cells for dates that are over 1 year ago. The dates that are within the last year do not show with red conditional formatting. But the nice thing about using the TODAY() formula is that is will always be updated when I open this file. If you would like to practice this lesson with my spreadsheet go ahead and click Conditional Formatting Dates over 1 year old.xlsx

Update as of Sept 19, 2014. I wrote a new post that covers this topic in more depth.

https://stevechasedocs.wordpress.com/2014/09/19/how-to-format-dates-in-excel-that-are-over-one-year-old-to-turn-red-yellow-if-eleven-months-ago-and-green-if-between-one-and-ten-months-ago/

### Like this:

Like Loading...

*Related*

## About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at 5dollardinners.com.

This was very easy to do!!! Thank you for explaining it so simply 🙂

Glad to hear!

Steve

Steve, I am trying to create a condition format for dates that are coming up to 5 yrs and another for 10 years. Can you help?

It is great but it also acts on empty cells (no date), any idea on how to avoid empty cells?

There is a solution for blank cells. After you select the cells, click Conditional Formatting–>Highlight Cells–>Equal To: 0 with a custom format of no fill color.

Make sure that this rule is applied first in the order. It should automatically do this if this was the last rule you did. Verify the order from Conditional Formatting –>Manage Rules.

Hope this helps.

I dont understand the formula to leave cells blank. Could you write the formula out please.

Rob,

What version of Excel are you working with?

Steve

I am using Excel 2003. Sorry for the late reply back. I have gotten the cells to change color like you showed. I just now need to get cells that dont have anything in them yet to not change color.

Ah, in Excel 2003 the steps are different. In Excel 2003 you are allowed up to 3 Conditions. The trick would be to make Condition 1 no color if Cell value is equal to zero.

Then make Condition 2 with a formula =B1<=TODAY()-365.

After you write the formula and click ok, make sure you go back and remove the quotes around Condition 2.

Then use the format painter over the rest of the cells.

I captured some screen shots so email me at stevechase@live.com if you would like to see them.

Steve

How do create a traffic light system which shows amber as within a month and red is a late date?

Hey Jason,

An option you could try: Insert a column to the right of the dates. Then run the =day(cell) formula. This will give you a column with numbers 1-31 depending on the day for the date on it’s left.

Then select that columns with numbers 1-31 and apply the traffic lights conditional formatting. Click Manage Rules to edit the settings. First choose to reverse the icon order. Then select Show icon only. Make the traffic red light be when values are greater than or equal to 20 (Number). Change the default from (Percent to Number). You could then make the amber cells from 10 to 19 and the green likewise be less than 10. Or whatever numbers you want to make the threshold.

Make the column tiny and it will be adjacent next to the dates on the left.

Hope this helps with an idea.

Steve

Thank you buddy! No one in the net has explained it so clearly. keep up the good work!

I have this working but I am looking to edit this formula so it allows me to enter a date in a cell and then it highlights the dates that are earlier than that date I have entered. Can you help me, please? Thanks so much!

Dave,

To set this up, first enter a date that you want to test against. Let’s say that the date you want to compare to others is in cell A1.

The first step would be to enter the date in a cell like A1. Then select the list of other dates. Let’s say your dates are in cells C2:C18. After you select those cells with all the dates then click Conditional Formatting–>Highlight Cell Rules–>Less Than.

Make the cell box =$A$1 where it reads

Format cells that are LESS THAN:

with a color you like from the dropdown to the right.

Steve

If I want to condition a list based on today’s month to highlight regarless of year how do I do that on excel (latest version)

1. Select your dates

2. Click Conditional Formatting (HOME tab)

3. Click Highlight Cell Rules > A Date Occurring…

4. Select “This Month” from the drop down box that reads A Date Occurring: Format Cells that contain a date occurring:

Thank you however this method only works if the date is of this year but I wanted it to highlight cells of this month but have different years to it. Do you know if it is possible to do that.

You can make it work with this formula.

=MONTH(A1)=MONTH(TODAY())

Where A1 is the first date in your column. Select all the dates first. Then click condtional formatting–>New Rule–>Use a formula to determine which cells to format.

enter the formula from above where A1 represents the first date. Then click the format button and select the color fill you desire.

Source credit here http://www.mrexcel.com/forum/excel-questions/334761-conditional-format-current-month-year.html

Hello Steve, I’m using MS Excel 2010, I have a worksheet which i use every week and it contains multiple date columns (Target date), what I wanna try is a formula that could help turn those columns red when they pass their respective dates unless a Actual date is filled in the column below it which says Actual date .. please help

You can compare one column or date to another column of dates using the greater than or less than operator. Lets say that column A is your actual dates and your column B is your target date. First select all the target dates. Then click conditional formatting –> New Rule. Select use a formula to determine which cells to format. Then type =B1<A1. Or if you have a header row you would type =B2 greater than if desired.

Make sure that formula does not have any $ signs for absolute references.

hope this helps

Thank You, so much clear simple precise couldn’t ask for a better explanation

thanks

Hi! This is great. Do you know how I could apply this any date that is later that the first of each specific month. I am trying to do this in order keep track of bills from tenants being turned in on time/late. I want the function to highlight any date that is later than the first for each month. How would I go about this?

Liz,

What do your columns look like for the bills you have for your tenants. Are you recording transactions for bills that are always due on the first of each month?

You could check out the format cells for “A Date Occurring” and select last month. That might work. In Excel 2010 or 2013 (I also think this works in Excel 2007) you select all the dates, then click the Conditional Formatting command, then “Highlight Cell Rules –> A Date Occurring.

Otherwise an alternative is to mark yes or no for bill paid in a new column then conditionally format based on text for all the cell that have “no.”

This is so simple, thank you!

I’m creating a training tracker and want certain dates to highlight red when they are out of date, but remain green when they are in date… can I apply two formats in this way?

Furthermore if somebody completed training in January 14 and I want the cell to change to red in January 15 – would I still use the same formula?

Alex,

Yes, Excel conditional formatting can have multiple rules applied to the same cells. So the red and green colors would be possible. When you are in the rules manager you would create 2 rules. They work in order of precedence from top down. The green cells could have the formula =B2>=TODAY()-365.

The Jan 14 and 15 example sounds like it needs some other kind of metric like complete or non-complete status to make it work. Possibly link to a status column to make it colorful based on the conditional rule that is set to make text equal to.

Hope this helps a little. Good luck with the project.

Steve

I am working on a training sheet in excel 2010, I would like the date to turn red if it is more than 3 years old and if possible turn yellow if it is within 2 months of being 3 years old. I have tried doing -1095 since that is the 365 * 3, however it did not work.

Alyssa,

First check to see if you have any conditional formatting rules applied. Then try to select all the dates you want to work with. The formula would look something like this for red cells =B2<=TODAY()-1095.

Apply another rule for the yellow cells as =B2<=TODAY()-1035 for 2 years 10 months.

Make sure that there are not dollar signs or apostrophes in the formula as they sometimes appear after you write the conditional formatting formula.

Good luck, it should work out.

Steve

Hi Steve,

I have already set the date to change color on my spread sheet if the date is over a year. What I would like to happen is if the date is expired I want it to reflect a “NO” in a separate column. If it is a valid date I want it to say “YES” (document is still valid).

date name patient info yes,no

can you help with this?

Karen,

I would use the IF function. It is one of the logical functions that test a condition then renders a value if it is true or false. An example

=IF(formula_test,”yes”,”no”)

This would place a “yes in the value if the test was true and “no” if the test was false.

The IF function is on the formulas tab under the logical button.

Thank you for your help,

Thank you for this, really helped me out! 🙂

I am tracking the training status of the people on my project. All training has to be taken annually.

I want to set it up where the cell turns green if the date in the cell is between 1 and 333 days from today’s date

i want to set it up where the cell turns yellow if the date in the cell is between 334 and 364 from today’s date

I want to set it up where the cell turns red if the date in the cell is 365 days or more from today’s date

I also want to turn the cell red if there is no date in the cell

Charles,

Assuming the first date cell is B2 and you select all the dates in the column then go to Conditional Formatting –New Rule… Use a formula to determine which cells to format.

Red

=B2=TODAY()-333,B2=TODAY()-364,B2<=TODAY()-334)

Any blank cells will turn red by these rules as well.

I uploaded a sample spreadsheet you can download from my student OneDrive folder. The link is http://1drv.ms/1pl0ERY

The file is called Conditional Formatting Red Green Yellow dates.xlsx. You won't be able to view the conditional formatting from the free Excel online browser but you can open it from the desktop Excel.

Steve

Sorry the last comment reply did get messed up on the html for some reason.

Let me clarify the yellow cells will be =AND(B2>=TODAY()-364,B2=TODAY()-333,B2<=TODAY()-1)

And the red cells will be =B2<=TODAY()-365. This also will include blanks.

If you want me to email you the file that has all the notes and sample data, send me an email at stevechase @ live.com

Update: Unfortunately, my formula codes that were written so nicely in the comment when I posted are still getting messed up in the html editor. So please go to this link to get the Excel file I created that has all the formulas. The Excel file can be downloaded here http://1drv.ms/1pl0ERY

Thanks Carolina!

Thanks for the help Steve!

You are welcome. I’m actually going to just write a blog post on this topic since I think it is really cool. So I will share the link with you later in this thread.

Here is the post. https://stevechasedocs.wordpress.com/2014/09/19/how-to-format-dates-in-excel-that-are-over-one-year-old-to-turn-red-yellow-if-eleven-months-ago-and-green-if-between-one-and-ten-months-ago/

Pingback: How to Format Dates In Excel That Are Over One Year Old To Turn Red, Yellow If Eleven Months Ago, And Green If Between One and Ten Months Ago | Steve Chase Docs

Hi I am trying to create a spreadsheet using traffic lights in (2010) to show when a document review date has expired. One column will show the actual date of review, the next will shown the scheduled review date and the next column will contain the traffic lights,

green if carried out before or on the scheduled review date,

Amber if between 1 and 30 days overdue and

red if over 30 days overdue.

At the time of review the actual date column will then be updated to reflect the new date . The scheduled review column will also be updated to show next expected review date. Can you advise me best way to handle this please.

Hey Stephen,

Please visit this link to view my response on how I would apply the traffic lights.

https://stevechasedocs.wordpress.com/2014/10/02/how-to-use-excel-traffic-lights-with-conditional-formatting-dates/

Pingback: How to Use Excel Traffic Lights with Conditional Formatting Dates | Steve Chase Docs

Thank you very much will give this a try

Hi Steve your link worked great. Thanks for our assistance

Hi after having created the spreadsheet I have had another thought on the amber traffic light, can you advise me please how I would change to reflect that the review date has 31 days left before review date.

Therefore

Green would be in date 1 or less days

Red would be out of date greater than 1

Amber would highlight that there is 31 days left before review is required.

Thanks for your assistance

I can’t think of a solution because of the difficulty of involves tracking a date that has not yet been inputed for the amber lights to work. If I later think of a solution, I will let you know.

Thank you

Hi Steve not sure if this will help you with a suitable formula. The reviews are carried out every three years and the green traffic light will turn Amber on day 1064 indicating that the review is due , if it is not carried out by day 1095 (3 year timescale) the Amber light will turn red and if it is carried out it on or before day 1095 it will turn green.

Hi Steve, I’m working on another spreadsheet in 2010 where traffic lights have to be used. green when the value of column C is equal to or greater than column B and red if column C is less than column B. Can you give me any advice on this please. Thank you

Stephen,

This can be done with a formula in Column A that has Column C minus Column B. Then select the values in Column A where the traffic lights would go. Create a new rule with traffic lights icon sets that would be green if the column A value is greater than or equal to zero. Also red traffic light if less than zero. Make sure you change the type from percentage to number. Finally check the box that reads “Show Icon Only”

Steve

Thanks, will give it a shot.

Steve your advise was very helpful, but I have a slightly different problem. Instead of using today’s date in the formula, I need it to use the date I put in the cell. What should my formula be instead of using Today?

Willie,

Instead of using the TODAY function you can type your date mm/dd/yyyy in a cell as your target date. Whichever cell you pick is fine as long as you use absolute reference to it in the formula. Let’s say you choose A1 to be the cell you enter 12/1/2014. When you refer to cell A1 in the formula it would be $A$1 instead of today function.

I created a template spreadsheet you can open from my public OneDrive training files here. https://onedrive.live.com/redir?resid=30B662933B979F63%2132147

It will open in the Excel online browser but then you can download it to the full desktop version of Excel to make a copy of the file on your computer.

If you can’t get the file download, then email me at stevechase @ live. com and I can attach the file to you.

Steve

I got it to download but not quite what I need. I’m making a training database with only dates and I need the cells to change color to red when it gets within a certain timeframe. Like 90 days from the date that is in the cell or once a year or turns yellow in 90 days then red in 180 days. But it has to be based on the date in the cell. I think you can see where I’m trying to go.; I used the formula =$a$1<=today()+365

This did not work at all, I think there is something I'm not grasping

OK, try removing the absolute reference on the A1. It would look like this =A1<=TODAY()+365. This should work if your column A has all the training dates on it. You would need this to be relative reference on the first cell that contains a date in your list. Hope that helps. If not you can send me an example of your file to stevechase@live.com so I can give it a look.

I hope you still check this site, I am having trouble and have searched everywhere. I am trying to have a Row turn yellow if its 11 months turn orange if its 12 months turn red if its 18 months and turn dark red if its 24 months from the today. I tried some of your formulas but I’m stuck at this point.

Jon, this would require 4 formulas. First select all the data and when you write the formula you will need to absolute reference the column with the date in it but leave the row relative. For example lets say your first date is in cell A2 so you select A2:C500. =$A2<TODAY()-730 for dark red. You would repeat the formula and make sure that they are sorted in dark red, red, orange and yellow. I did an example spreadsheet you can download in Excel from my OneDrive account. https://onedrive.live.com/redir?resid=30b662933b979f63%21105875

Thank you so much for replying, this is an awesome site, I have one more question if possible. I am creating a task like and each tab is a month i.e. May 2015, June 2015 and each task is marked Not started, In-Progress and Completed. If a task is Not started or In-Progress I would like it to Copy over to the new Month i.e. if its June then it will check May’s tasks and copy over remaining tasks. If they are completed though I dont want them to be copied over. Is there a way to do this and I am surprised it hasnt been done already. If there is not do you have any thoughts on this ?

Jon,

I do not know of any templates that have that feature. With out creating a custom macro, my recommendation is to make your list have filters. Then I would filter out all but “completed” and copy and paste those records to a new tab.

Steve

I can not get this to work. I have done it over and over and over and over…

Jill,

I would make sure that you don’t have two many conditional formatting rules going on as they could offset the correct formula. One way to start over is to choose conditional formatting—>clear rules( from selected cells or entire worksheet).

Also you have to make sure that your dates are indeed formatted correctly as dates.

I created a template that has the correct formula from cells A2 through A10000.

Here is the file you can download. https://onedrive.live.com/redir?resid=30b662933b979f63!156868&authkey=!AH0sshqONBJZm0g&ithint=file%2cxlsx

Hi Steve

This has probably already been asked. I have a spreadsheet with “Last review date”and “Next review date”. I have the last review date already typed in and want to have the next review date appear which is every 2 years. I also would like to have the next review date, which might be already overdue in red. Is that possible? Thanks for your advice.!

Elba, Would a formula work to get the “next review date”. If so, all you would do is =last date + 730. Make sure the column is formatted for the date not number. It is possible to see red format if the date is past due. Select the dates then click the conditional formatting button in Excel 2007, 2010 or 2013. Select new rule then use a formula to determine which cells to format. Assuming B2 is your first date cell then you would type = B2 < TODAY().

Okay, so in 2013 version (Example: employee start date and training dates)

So in A1 i enter in a start date

B2, C2, D2… is where I would enter dates of training, how do i get those dates to show up as RED if they are 90 days past the date i entered in A1 (which is not necessarily today’s date) Does that make any sense?

Lia,

To clarify, are you recording multiple training dates. The more training a person has, the more dates are entered going to the right…. ie. B, C, D, E, etc.

The question is do you want to test everything back to the employee hire date? If so, then you would need to start with selecting column B training date. 1. Make your selection with B2 and then all the way down to your last cell in column B. The formula would be =$B2>$A2+90

Repeat the formula again in column C where it would be =$C2>$A2+90

Thank you. That part worked, but now I need it more complex than I was originally instructed and this I am not sure I can even explain. Thanks for your assistance.

Hello,

Im trying to get 1 cell for dates and another cell to recongise that the first cell is overdue.

Example;

I called my client on the 13/08/2015. Now it is two weeks I would like the cell next to it to show an OVERDUE?

Can you please help 🙂

You can do this if you want to make a new column with the text “overdue”. You would need to write the formula in the IF function. The IF function has three arguments. First the condition you wish to test which leads to either true or false answer, then what happens if it is true and then if false.

=IF(formula, “OVERDUE”, “—“)

The formula would be something like =A2<TODAY()-14

Steve, I am trying to create a condition format for dates that are coming up to 5 yrs and another for 10 years depending on the type of access. Can you help?

(Sample;

Column-A Column-B

Date Type of access

11-Feb-2011 s

6-Feb-2005 c

13-Apr-2013 s

22-Feb-2013 ts

Ozy, You can use the DatedIf function which is a formula. Say your dates start in cell A2 and you want to format red any cell that is greater than 5 years ago. Create a new conditional format for using a formula =DatedIf($A2, Today(), “d”) > 5.

Do the same with greater than 10 and make sure that is higher on the list of formatting rules.

Here is a great article on how to use the datedif function.

http://www.cpearson.com/excel/datedif.aspx

Correction the “d” should be a “y”

Ozy, You can use the DatedIf function which is a formula. Say your dates start in cell A2 and you want to format red any cell that is greater than 5 years ago. Create a new conditional format for using a formula =DatedIf($A2, Today(), “y”) > 5.

Do the same with greater than 10 and make sure that is higher on the list of formatting rules.

Here is a great article on how to use the datedif function.

http://www.cpearson.com/excel/datedif.aspx

Steve, please create a vid and send me the link. This is not working for me. The formula I am using formats all cells red with dates that fall between NOW and 10 yrs.

Steve, it didn’t change anything. Here are some dates that I am working with that start on cell C2.

Date Accessed

1-Jul-2014

11-Feb-2013

6-Feb-2007

13-Apr-2008

22-Feb-2010

17-Apr-2005

26-Jun-2012

12-Aug-2006

25-Mar-2011

16-Apr-2009

Thanks for replying to my post Steve…..

Are you looking to create 2 separate conditional formatting colors. Or do you just want 1 conditional format if the date is between 5 and 10 years ago? If the later is the case, then this should work.

=AND(DATEDIF($C2,TODAY(),”y”)>=5,DATEDIF($C2,TODAY(),”y”)<=10)

Here is a link to an example spreadsheet. https://onedrive.live.com/redir?resid=30B662933B979F63!352787&authkey=!ADp1fbV_f57GI5s&ithint=file%2cxlsx

https://stevechasedocs.wordpress.com/2016/02/15/using-the-datedif-function-in-excel-for-conditonal-formatting-expired-training-dates-video/

Here is my latest video post that explores more on this topic with the DATEDIF function. https://stevechasedocs.wordpress.com/2016/02/15/using-the-datedif-function-in-excel-for-conditonal-formatting-expired-training-dates-video/

Steve, I followed your formula, but it didn’t want to work out. So I gave up on it. But here are some dates I was using on my excel doc;

Invest Date Approve Date Eligible

10/28/2013 6-Nov-2013 AS 8-Nov-2013

5/16/2007 1-Nov-2007 DE 14-Nov-2007

6/29/2011 10-Aug-2011 DS 13-Jul-2012

12/13/2005 18-Feb-2006 BJ 28-May-2006

11/2/2008 1-Dec-2011 TK 4-Dec-2011

2/13/2013 23-Feb-2013 LO 25-Feb-2013

5/25/2006 5-Jul-2006 TR 9-Aug-2006

11/25/2013 13-Dec-2013 FC 17-Dec-2013

So these are some dates that I needed to create a formula. I needed to either highlight or change font color once the Approve date was either at 4 yrs or 9 years, depending on other criteria. Didn’t understand if I was entering the formula correctly or in the right place.

This formula would give you true if it is either 4 years or 9 years. =OR(DATEDIF(B2,TODAY(),”Y”)=4,DATEDIF(B2,TODAY(),”Y”)=9)

Steve, I finally got the formula to work. I am working in a different location, so it might have been the actual work place that wasn’t allowing me to be happy there and nothing was working right for me. Thanks for your assistance. I know now who to ask for assistance in the future.

Thanks Ozy

I have a spreadsheet with due dates in column F I want the row to highlight in yellow when the date is 60 days before the due date in column f and highlight in red when 30 days before the due date.

Hi Pamela,

I have a solution example that you can download from this OneDrive link. https://onedrive.live.com/redir?resid=30B662933B979F63!359970&authkey=!AFyGV6IoxdKkN_Y&ithint=file%2cxlsx

The idea is to take select all the data starting in the second row. The red cells formula should be above the yellow rows conditional formatting in the manage rules dialog box.

Red rows formula =$F2-TODAY()<30

Yellow rows formula = $F2-TODAY()<60

Steve

I’m trying to highlight dates that are under a year old. I’ve tried multiple formulas but nothing seems to work. I can create a formula in a separate cell that works, (=IF(TODAY()>=B5+365,”over year”,”under year”)) but I don’t want a second column, I just want the originating column with the dates to change color. Please help 🙂

try this formula =DATEDIF(A2,TODAY(),”y”)<1

Here is a link to a sample workbook I created to demo this. https://1drv.ms/x/s!AmOflzuTYrYwmL4j1BNhZv-LNdWx6w

Hi Steve, this site is fantastic! I am wondering if you can assist me….

I have a database for training where we enter the training date. i would like the cell to change colour – green if the training is less than 30 months (2.5yrs) amber for anything greater than 30 months but less than 36 months, and red for anything older than 36 months…

also if the blank cells cold remain white that would be cool…. is that possible?

I used your example sheet to get them to turn red but I dont have the brains to figure out the rest of the formulas 😦

https://1drv.ms/x/s!AmOflzuTYrYwmP8l1BNhZv-LNdWx6w is a link to a spreadsheet solution.

Here is a video that will share with you how to do this. https://stevechasedocs.wordpress.com/2016/02/15/using-the-datedif-function-in-excel-for-conditonal-formatting-expired-training-dates-video/