How to format dates that are over 1 year old to turn red in Excel using conditional formatting.

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.

Last Training Dates

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

Conditional Formatting 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.

verification of red cell of conditional formatting at work

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

copy and paste the formats with format painter or paste special...formats

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.



About Steve Chase

I want to help you work in your business, so you can work on your business. I want to help you achieve your goals and help you have a successful small business journey. I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business. But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers. My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant.
This entry was posted in Excel and tagged , . Bookmark the permalink.

94 Responses to How to format dates that are over 1 year old to turn red in Excel using conditional formatting.

  1. Natalia says:

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

  2. Frank says:

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

    • Steve Chase says:

      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.

      • Rob Johnson says:

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

      • Steve Chase says:


        What version of Excel are you working with?


      • Rob Johnson says:

        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.

      • Steve Chase says:

        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 if you would like to see them.


  3. Jason Page says:

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

    • Steve Chase says:

      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.


  4. Kowshi says:

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

  5. Dave says:

    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!

    • Steve Chase says:

      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.


  6. izz says:

    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)

    • Steve Chase says:

      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:

  7. Partap Kumar says:

    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

    • Steve Chase says:

      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

  8. Travis Landry says:

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

  9. Liz says:

    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?

    • Steve Chase says:


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

  10. Alex says:

    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?

    • Steve Chase says:


      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.


  11. Alyssa says:

    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.

    • Steve Chase says:


      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.


  12. Karen M says:

    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?

    • Steve Chase says:


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

  13. Carolina says:

    Thank you for this, really helped me out! 🙂

    • Charles says:

      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

      • Steve Chase says:


        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.


        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

        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 Chase says:

        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 @

        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

  14. Charles says:

    Thanks for the help Steve!

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

  16. Stephen says:

    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.

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

  18. Stephen says:

    Thank you very much will give this a try

  19. Stephen says:

    Hi Steve your link worked great. Thanks for our assistance

    • Stephen says:

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

      • Steve Chase says:

        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.

  20. Stephen says:

    Thank you

  21. Stephen says:

    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.

  22. Stephen says:

    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

    • Steve Chase says:

      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”


  23. willie larry says:

    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?

    • Steve Chase says:

      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.

      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.


      • Drac Larry says:

        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

      • Steve Chase says:

        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 so I can give it a look.

  24. Jon Philipson says:

    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.

    • Steve Chase says:

      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.

      • Jon Philipson says:

        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 ?

      • Steve Chase says:


        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.


  25. jill says:

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

  26. Elba says:

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

    • Steve Chase says:

      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().

  27. Lia says:

    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?

    • Steve Chase says:

      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

      • Lia says:

        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.

  28. Sam says:


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


    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 🙂

    • Steve Chase says:

      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

  29. Ozy says:

    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?

    Column-A Column-B
    Date Type of access
    11-Feb-2011 s
    6-Feb-2005 c
    13-Apr-2013 s
    22-Feb-2013 ts

    • Steve Chase says:

      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.

      • Steve Chase says:

        Correction the “d” should be a “y”

      • Steve Chase says:

        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.

      • Ozy says:

        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.

  30. Ozy says:

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

    • Ozy says:

      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.

      • Steve Chase says:

        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)

    • Ozy says:

      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.

  31. Pamela says:

    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.

  32. ReneeRox says:

    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 🙂

  33. kristy says:

    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 😦

  34. Carrie says:

    Fantastic! This is just what I was looking for! Thanks for the help.

  35. Hi Steve – Great information! Your examples are so helpful – they helped me figure out my need. I thought I would share with your fans. I have a set of columns where Row 1 contains dates beginning with my project start date (in D1 for this example), say 7/15/2017. Each column to the left is incremented by one month using =EDATE(D1,1). I wanted format the fill color for odd-numbered years in green and even-numbered years in blue. This helps identify calendar year with alternating colors. After much experimentation, I figured out the following formula, =ISODD(YEAR(D$1)) followed by formatting for odd numbered years and then =ISEVEN(YEAR(D$1)). Now when I change my start date, the year’s to the right update their format. I learned to use the cell D$1 rather than D1 to apply the same format logic to cells below the nearby copying my conditionally-formatted cell and using Paste Special (Formats) to cells below.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s