I’m writing this post in response to a comment that Charles wrote in my “How to change the color of an Excel date to red if over 1 year old” post. It was such a good comment I thought I’d share my thinking with the rest of you in a new post.

“*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.” – Charles*

Let’s start with making the dates turn red if the training date has occurred over 1 year ago. In my screen shots below I’m using date cells that start with B2 so I select the first date all the way down to the last date. I click Home tab–>Conditional Formatting –>New Rule. Then I choose “Use a formula to determine which cells to format.”

I click format and select a red fill and then I write the formula.

**=B2<=TODAY()-365**

The **TODAY()** function is volatile and updates to the current date. Excel has a serial number value for each date. For example Jan 1, 1900 =1, Jan 2, 1900 =2, Jan 3, 1900 =3, Jan 4, 1900 =4 and Sep 19, 2014 =41,901. Everyday is another plus one. So we can use the greater than > and less than < operators to compare dates against the **TODAY()** function. You have to think in numbers and older dates will always be lesser than newer dates. So if you were born a long time ago you actually have a smaller Excel number than a someone from a younger generation. And this is coming from one who just bought his first pair of reading glasses. 🙂

After I create the first conditional format with red cells, I select the same cells and repeat the setup for the formula new rule for yellow colored cells. This will format cells yellow if the training date occurred 11 months ago.

**=AND(B2>=TODAY()-364,B2<=TODAY()-334)**

This uses the logical **AND()** function which looks at 2 conditions to check if the date is at the 11 month ago mark. The **AND()** function looks to see if all conditions are true. Specifically, it checks to see if the date is between 364 and 334 days ago. So it will turn yellow to help recognize a date that is close to reaching the 1 year mark.

The final step is to select the date cells then click the create new rule for conditional formatting with a formula.

**=AND(B2>=TODAY()-333,B2<=TODAY()-1)**

The Rules Manager is used to make edits to the conditional formatting. So if your rules gets messed up you would need to click Conditional Formatting –> Rules Manager.

Note: When you are writing the formula you do not want to use your mouse to click on the first cell of dates because it would make B2 absolute referenced to $B$2. If that happens just delete the $ signs. You only need to have a relative reference and Excel will automatically apply the formula to all cells selected. The “apply to” section should have the absolute reference since that is the range that is not being copied anywhere. The format painter is also a nice choice is you want to copy the conditional formatted cells from one worksheet to another. Even works from one workbook to another workbook. So feel free to download my sample file spreadsheet that has all these rules in it.

If you like this post and be sure to sign up for the mailing list so you will receive an email for each of my new post. And be sure to check out my YouTube video on Conditional Formatting examples.

New Post update video that uses the DATEDIF function which I believe is more elegant. https://stevechasedocs.wordpress.com/2016/02/15/using-the-datedif-function-in-excel-for-conditonal-formatting-expired-training-dates-video/

On a parting note, be sure to stop by my wife’s blog to see what’s for dinner tonight.

Pingback: How to format dates that are over 1 year old to turn red in Excel using conditional formatting. | Steve Chase Docs

Hi Steve, there’s another way, try with DATEDIF function, it solved the 365/366 days.

Thanks Christian,

I never knew about the DATEDIF function. I will look into it for sure. That would help with leap year.

Steve

I just found this awesome article about the DATEDIF function. http://blogs.office.com/2013/04/04/using-conditional-formatting-to-highlight-dates-in-excel/

Steve, thank you for your informative post! It has been the only one that has worked for me. I have a question… What if I want an entire row to be highlighted, not just that individual cell? So if a cell has a date that is more than 30 days old, I would like the entire row in which that cell is in to highlight.

Carlos, you would need to make an absolute reference in the formula to the Column. So it is important that when you create a rule with a formula it is referencing the top left most cell. So let’s say that B2 is the top most left cell. Then you would need to specify this by $B2.

To modify existing cond. formatting rules, first click on any date that is within the range. Then click Cond. formatting –> Manage Rules. Click “Edit Rule…” and replace all cell referencing with an absolute reference in front of the column. Then make sure you update the “Applies to” range. So instead of just one column, make it stretch to the last column. All cells in the Applies to section will be fully absolute referenced.

It is just the actual formula that will be mixed, that being only the Column.

Hope this helps. Here is a link to a sample spreadsheet. http://1drv.ms/1xV5UFq

Steve, thank you for your prompt response, I really appreciate it!… You wouldn’t happen to know how to do this exact process on Google Sheets would you? I got it down on excel but I can’t seem to get it on Sheets. Just a shot in the dark. Thank you again, I really do appreciate it. Take care.

Carlos,

I know you can do conditional formatting with a column in Google Sheets. But formatting a row doesn’t look like an easy option to me, if it can be done at all.

The format a date if it is over a year old can be done like this:

Select column then click Format menu–>Conditional Formatting

Date is before: exact date: =today()-365

Then pick the background color option.

That should work fine with just one column in Google.

Thanks for all your help Steve! Happy Thanksgiving!!!

you too

Hi Steve, the last comment you had for me on using the date in the cell to use to turn the date red at a cert time was; A$1$ >(1/1/2015)+365. Would this be the language if I wanted to use a date in a cell to turn red in 1 year. This of course is an example. Also Can I use format painting to do other cell on the same sheet? or does the ($) mess that up?

Thanks man, Drac

I believe that the formula example would work if you broke it down by looking at the date serial numbers. If you don’t use today function then I would separate the date in a cell reference then add 365 in a different cell. I’m sorry but I might not understand fully the question. Also, the format painter would mess up if went to different columns. I found an excellent video on youtube that I think will help explain the absolute reference with conditional formatting. https://www.youtube.com/watch?v=6JqgNFV9WZg

Hi Steve.

How would this work if I want to apply this to a range of cells, but the dates have not been populated yet? It would turn all my cells red. How can I avoid this outcome?

Phillip, Did you try it yet? I don’t believe your cells would turn red if they are blank. I have tested this out before and I remember blank cells were skipped. It is interesting that other conditional formatting rules on numbers do apply if the cells are blank. Let me know if you still get red cells on blank values after applying the rule.

Steve

Hi Steve. I do get the red cells when I apply this forumla. I’ve even tried adding a second formula that says if less than 365 days, don’t change the color, but it doesn’t take precedence over the first formula. I’ve also tried changing the order of execution of the formatting but that hasn’t proven successful either.

Hi Phillip,

The solution is to create a new rule with a formula that seeks to test if a cell is equal to blank.

You use two double quotes “”back to back to represent a blank or null value.

So the formula could be =B2=””

Make the conditional format to fill with no cell color.

Also make sure that the rule is first at the top of the order or rules. There is also a check box that reads stop if true just in case you need it.

Also, just wanted to correct my prior comment that I agree with you that the cells are turning red on blanks because I just tested out the formula on blank cells.

Hope this helps,

Steve

Thanks alot for this one! I just wondered if it’s an way to not get red in empty cells, I know I can just unmark them, but that’s the hard way 😀

Tom, I’m wondering if you created a new rule that looked for blank cells and you formatted it with no color fill. As long as the rule was placed first in the order of the manage rules dialog box.

The way you test for a blank is to type two double quotes back to back.

“”

Would look for a null value.

range=””

Hope that help save you time.

could you please post a tutorial on how to achieve this same outcome but sole the 365/366 day problem please. Either a small fix in this tutorial or direction to a tutorial that solves this? thank you

Mike, This webpage has some great help on the datedif secret formula. http://www.excel-exercise.com/function/date-time/datedif/

If I’m using the datedif formula it has 3 arguments. Datedif(old date, new date, a parameter on the return gap) The return gap of “y” would be years.

So let’s say you want to compare Feb 29, 2000 to Feb 28, 2001. If you did a datedif formula with the third parameter of “y” you would get 0 years. But if you did Feb 29, 2000 to March 1, 2001 the datedif would pick up on it being 366 days and thus be 1 year result.

Example =datedif(A1,B1,”y”) where as A1 is older than B1.

Just got to make sure that you use the right absolute referencing in the columns when you do the conditional formatting formula.

Steve

solves*

Mike, Also as a second comment I have a spreadsheet example that works out the Feb 29, 2000 base date to 365 and 366 days forward with datedif. It is on my OneDrive folder here. https://onedrive.live.com/redir?resid=30B662933B979F63%2146791

Hi Steve. This was very useful. Do you know how I can make the alert a word like EXPIRED in a new cell rather just highlighting the date red. This way I could sort and see numbers that are due?

Also I have a report that looks roughly like below. It just spits out the staff, Course and start date. Each course expires at different times. I wanted to see if I can set up 1 formula up that will spit out an “Expired” or “No expired” cell that I could copy down. So something like if “induction” is over 1 months= EXPIRED, If Culture is over 6 months =Expired etc

Start Date Expire

Induction (1 month) 1/1/205 EXPIRED

Culture (6 months) 1/1/2015 NOT EXPIRED

Risk (12 Months) 1//1/2015 NOT EXPIRED

Vidya,

The IF function will work for you to generate the words “expired” and “not expired”.

Assume that your first date cell is in A2 and you want to create the calculation formula running down column B. So you could enter in B2 =IF(TODAY()>=A2-365,”Expired”,”Not Expired”)

For your second question, are you putting Induction, Culture and Risk in the same column?

Maybe something like this would take care of the only the first condition =IF(AND(TextCell =”Induction”,TODAY()>=B2-30),”Expired”,”Not Expired”)

Hello Steve,

Great article! I attempted to change the formula to reflect months instead of days. I have training requirements that expire after, 12, 24, 36, or 18 (don’t ask) months. If they are due the current month or have expired, I’d like to have red, then yellow (due next month), or green (still current).

Here is the basic formula that I used:

=I4<=MONTH(TODAY())+12 (this would be red)

Where am I going wrong?

Jon,

The month formula is going to return a number 1 -12. But if you compare that with a date that is in I4 then I4 would equal to 42132 if it was 5/8/2015.

Which date field are you trying to compare? Are you going off of the initial training date in the past? Or do you have another date field that displays when the training will be out of date?

Hi there, not sure if you covered this one? Its my s/sheet of SDSs. I have the date the sds was produced and want it to highlight when 4 1/2 yrs old… it got to tekky for me there…sorry

Janice, sorry for the late response as I was going through my reader comments I forgot to respond to you. If you haven’t solved it by now you can get this to work with a new conditional formatting formula. First select the cells you want to apply it to. Then click home, conditional formatting —>New Rule. Choose use a formula to determine which cells to format. The formula would be use 1642.5 for days equal to 4 and half years. You could type your formula as =B2<=TODAY()-1642.5

Hi Steve.. I can’t seem to get this to work for green.

Basically, my requirements are exactly the same as the original commenters: I have a range of dates from a few days from now to about 11 years ago. I want dates more than 5 years ago to be red, I follow your instructions for red and change the days from 365 to 1825 and it works. I want dates ranging from 3.5 to 4.999 years to be yellow. Again, I follow your instructions using the AND formula and it works.

When I input the range for green (-1277 days to -1 day from TODAY) nothing happens.

No matter what, I can’t get the last set of dates to format.

Cheers.

Hey Chris,

The first thing I would inspect is the Conditional Formatting Rules Manager. Just click on any cell then click the home tab, conditional formatting, then “manage rules”. At the top of that box, you will have a choice to pick show formatting rules for: current selection or this worksheet. Change it to “this worksheet”. Hopefully you will find an additional rule that is overlapping your range and causing the conflict. If you identify 4 rules then you can delete the extra one.

The next solution to try if that doesn’t work is to just carefully double check the green formula. You could start over by deleting the green rule. Then edit the yellow which is working and copy yellow formula to the clipboard. Close yellow and click new rule from formula. Paste the yellow formula and make adjustments as needed with the -1277.

That thing to debug your green formula is to make sure that the TODAY function has an open and closing parenthesis after it. TODAY().

I need the same type thing, but for a year, 2 years, 3 years, from a specified date. Is there anywhere to find formulas written already? I just cant seem to get the formulas correct. haha

Kim,

The following rules would work but need to be in the order of precedence.

1 Year or less =B2>=TODAY()-365

2 Years or less =B2>=TODAY()-1095

3 Years or more =B2>=0

Make sure the order is top down as listed above. When you create a new rule it automatically goes to the top so you can click on manage rules and change to correct the order if necessary.

Awesome! Thanks, I was close….lol

that was a little tricky I might add myself

Thanks Again Steve, … it actually isn’t working exactly right. I put them in, in order, and i added one for blanks to be red. i tried it with and without the check for if its true. but there is something not exactly right. Thanks for trying to help!

Kim,

Maybe you should clear all the rules first and then retype the formulas. You can select the column and then choose conditional formatting–>clear rules from the selected cells or entire worksheet. The blank value I think should go first at the top of the order of rules and stop if true. Also make sure that if you click on your mouse while writing the formula the cell would be absolute reference. Like $B$2. You need to get rid of the dollar signs, especially the one in front of the 2 (second row) so that the conditional formatting relatively copies down the rows.

Steve

Hey Steve. I know this is a bit of an old post but I can’t seem to get it right. I basically cut and paste your formulas in. The only thing I changed was the B to an E because I am working in column E. I figure when I will need to do the same for column F after E is fixed. Today is 27 Nov 2015 and cell E5 is still white with a date of 13 Nov 2014 and the same for E51 with a date of 12 Nov 2014.

=E2=TODAY-364,E2=TODAY()-333,E2<=TODAY()-1) GREEN

These are exactly how I have the formulas. I am new to excel so it took me forever to get this far. If I delete the date from those cells they turns red, which I want so I know they need to do the training or turn in certs. I even changed the yellow to blue to see if it was just with the color. Changed other cell dates to try and get a yellow but nothing seems to work. I'm hoping you can help me fix this. Thanks.

Wow that did not post at all how I typed it

=E2=TODAY()-333,E2=TODAY-364,E2<=TODAY()-334) YELLOW.

Sorry, Eddie. It is frustrating that the comment thread does not post the comment as you type it. It has happened to me. I think it has to do with the less than and greater than symbols paired with equal signs.

=AND(E2>=TODAY-364,E2<=TODAY()-334) YELLOW.

=AND(E2>=TODAY()-333,E2<=TODAY()-1) GREEN.

=E2<=TODAY()-365 RED

Sorry for posting so much the formulas did not go threw right in the main post. The last 3 are what I have in my spread sheet.

Eddie,

I would first select the cells and then go to the manage rules dialog box. There you can find out if the order is correct or if you have too many rules and some need to be deleted. The main thing to look for is that when you are referencing the formula you need to write it using the first cell in that column. You could download this file, then use the format painter to copy between my worksheet and your worksheet the conditional formatting.

https://onedrive.live.com/view.aspx?resid=30B662933B979F63!9852&cid=30b662933b979f63&app=Excel

Really helpful. Thank you!

Thank you

Steve, How can I do the same thing in Excel 2003

Here are some screenshots I found from an online tutorial for Excel 2003. http://www.techonthenet.com/excel/questions/cond_format4.php

Hope that helps.

Some

Hi Steve,

Thanks for the great work, i do a fair amount on Excel, and always find your post helpful, i have another similar problem except i am working with expiry dates, and want cells to highlight red for expired and pink expires within 6 month and then orange if it is in less than a year, If the cell is blank or more than a year it does not need to be highlighted, i have tried a few options but don’t seem to get it right. Any advice on how to get it to do it

Thanks once a gain for the great work and looking forward to solving this issue.

Regards

Grant

Grant, assume that the dates start out in A2 and go down. Then in order to see if it is blank, you could write a formula with the “stop with true” check box marked in the rules manager. I will post the formula on this spreadsheet as the wordpress comment section doesn’t always literally post the formulas with the double quotes and parenthesis the right way due to HTML. But basically if it is equal to blank, make no format and stop if true. To make a cell check for blank, make it equal to “”. Two double quotes back to back.

https://onedrive.live.com/redir?resid=30B662933B979F63!348733&authkey=!ACgNKXq_xvi1B5I&ithint=file%2cxlsx

Exactly what I was looking for, Steve. Worked like a charm and very easy to follow. Just stumbled on to your blog by accident using Google search. I went ahead and looked at other posts of yours and there are quite a few things that can help me. Thanks for sharing!

excellent

Rick, here is my newest video post on the DATEDIF function. https://stevechasedocs.wordpress.com/2016/02/15/using-the-datedif-function-in-excel-for-conditonal-formatting-expired-training-dates-video/

HI Steve,

I want to change color if date is exceeding 0 to 7 days green 7 to 14 days amber 14 to 21 days red.. how to do that pls help me out..

Thanks

Najam

Hi Najam,

I put together a spreadsheet file that hopefully gets you in the right direction.Here is the OneDrive link which you can download. https://onedrive.live.com/redir?resid=30B662933B979F63!363732&authkey=!ADzitDyoQUtO020&ithint=file%2cxlsx

Steve

Hi Steve,

I was wondering if you could help me with my query. I have an excel sheet with 134 policies that need updating annually at different times. I’ve followed your instructions on how to format dates that are over 1 year old. How do I format this to show me when a policy require updating 30 days before it’s due. I’ve tried different options which worked but couldn’t get it to work with the annual format.

Help please!

Kemeisha

Hi Kemeisha,

Sorry for the really long delay in response. Here is something that might work for you.

=AND(DATEDIF(TODAY(),A2,”d”)>1,DATEDIF(TODAY(),A2,”d”)<=30)

Here is a link to download the workbook to see the formula. https://onedrive.live.com/redir?page=view&resid=30B662933B979F63!382935&authkey=!AHUbaPR94Prypog

Hope this helps point you in the right direction.

Hi Steve,

I have read through the comments and posts above but nothing seems to suit what I am trying to do.

I’m looking after a huge training matrix, which was previously looked after by someone else.

The training dates are put in by the date that the training was done, with the number of years the training lasts at the top of the column.

I’m trying to format cells so the highlight when the number of years is nearly due (3 months before it is due to expire) or has already expired.

E.g Training was done on 28/08/2011 so it would be due to expire in approx. 1 month (the certificate lasts for 5 years from the date) so I would like this to be highlighted in yellow.

How do I go about doing this? I’ve tried lots of different ways but cannot seem to figure it out.

Thanks

Jess

Hi Jess,

I created a sample solution file here for you. https://1drv.ms/x/s!AmOflzuTYrYwmL4l1BNhZv-LNdWx6w

=AND((B2+(5*365))-TODAY()>=0,(B2+(5*365))-TODAY()<=90)

Hi Steve

Thank you so much for the post. It helped solve part of the problem.

I would like to format the dates depending on the value in certain cell. For example, format dates that are over one year old to red, yellow if 11 months ago and green if less than 10 months ago if the value in cell C3 is “A” or “B”. And if value in cell C3 is “C”, “D” or “E”, format dates to over 2 years to red, yellow if 23 months ago and green if less than 22 months ago.

Can you please assist?

Regards

Lara

Hi Steve,

This is exactly what i am looking for, thank god for google! I have copied and pasted the formulas exactly and changed only the E2 to D6 as that is the first date cell. However only green and red seem to work, the yellow for coming up for expiry does not work? Any ideas?

Hey Dan, Here is solution file for you to download from my OneDrive folder. It should have what you need. I switched the the DATEDIF() formula which can present values in return of the month format. Hope this works for you to copy. https://1drv.ms/x/s!AmOflzuTYrYwmYE6Cx2FvtNlTt9kug

This was so helpful and exactly what I needed!!!! So glad there are clever experts like you around to help people like me. Thank you so much ha ha x

Wonderful.. I am glad to hear this.

some of my cells havent formatted, but when i check the manage tab in cond. format it says i have the rules applied to that cell,

Thanks

Not certain how to advise troubleshooting. Maybe first check to clear any rules are overlaying it. Another trick is to use the format painter to copy the conditional formatting from 1 cell that is working to a group of cells you want to apply the same rules to.

Hi Steve,

I’m having a lot of trouble carrying out the formatting so that it highlights the entire row. Basically, I’m dealing with applications and need to highlight the entire row once an application is expired (basically, if the submission date is 6 weeks prior to today’s date.

Assuming that my active column is E (submission date), I’m trying to use

=$E<=TODAY()-56

However, it keeps highlighting the entire spreadsheet.

My other formatting, =$P1="yes") (for whether or not they're approved) works fine for highlighting just that row.

I've tried clearing all formatting, but nothign seems to be working. Can you see any glaring problems or have any suggestions?

Here is a link of a spreadsheet example that you can use as a template. https://1drv.ms/x/s!AmOflzuTYrYwmZAw1BNhZv-LNdWx6w

Assuming your first date is in the second row because you have a header row. You would want to select all the table cells except the header row. The formula would be =$E2<=TODAY()-42. I think all you need is to change the $E to $E2 if the front part of your formula. I replaced 42 (6 weeks * 7 days) My other blog post might help out https://stevechasedocs.wordpress.com/2016/12/30/highlight-rows-of-data-in-excel-with-conditional-formatting-formulas/