Excel 2007 : Color coding Dates

**CODO69** · 04-27-2009, 03:02 PM

Well I'll throw this out there like all other noobs, I haven't much experience in excel other than the usual f*rtin around with the basics. But I digress, at my job I've been put in charge of maintaining all of our fire extinguisher for several buildings and I need to keep track of the dates for when their due maintenance. What I can't figure out is how to do the formulas for the dates. I want to set it up so that 10 months out of the year the dates are in a green color, the 11th month I want it to auto turn yellow (to indicate that it's coming due for maintenance), and the 12th month turns red to indicate that it's due. Now obviously not every Fire ex. is due on the same month so it needs to be able to tell the differance throughout the year (i hope that makes since). I'm not to concerned about the coloring part of the format since I know how to do that, It's just the actual formula part I cant get. The best I can figure out is to have it change color on an exact date. Like I stated before, I need it for the WHOLE month. Any help would be greatly appreciated before I tare out what hair I have left.

CODO69

**ConneXionLost** · 04-27-2009, 07:08 PM

Hi CODO69,

I'm guessing this will work in Excel 2007 (because I don't have a copy).

Assuming the "Last Test Date" is in cell A1, then select cell A1 and using Conditional Formatting, set the first condition as:

Formula is =IFERROR(DATEDIF(A1,TODAY(),"m"),0)<1
Colour background red.

set the second condition as:

Formula is =IFERROR(DATEDIF(A1,TODAY(),"m"),0)<2
Colour background yellow.

Make the normal background colour of the cell green.

Obviously if your date is in a different cell, then modify the formulas accordingly.

Cheers,

P.S. - Hopefully, you can save your hair for a more serious problem!

**CODO69** · 04-29-2009, 08:28 AM

I appreciate the sppedy help but the problem is stil some what there. I entered the formula as you stated and sure it changes the color... but regaurdless of the date they all changed color. Then when I added the second formula, they all changed to that second color and wont change accordingly with the dates. Any Ideas?

**ConneXionLost** · 04-29-2009, 12:01 PM

Okay, I made a right mess of those formulas, Ugh!

Try this:

Formula is =IFERROR(DATEDIF(TODAY(),A1,"m")<1,0)
Colour background red.

set the second condition as:

Formula is =DATEDIF(TODAY(),A1,"m")<2
Colour background yellow.

Make the normal background colour of the cell green.

Sorry about that!

**squiggler47** · 04-29-2009, 12:45 PM

This might help, and some other users

Its something i was working on for maintainance, but works in this situation as well :-

**CODO69** · 04-29-2009, 03:23 PM

Ok now were starting to get somewhere. ConneXion, I used your formula for the yellow blocks ( =DATEDIF(TODAY(),A1,"m")<2 ) and it worked. I had to change the <2 to a <1 because the 2 made the blocks change to yellow up to 2 months out instead of the 1 i required, no big deal crisis adverted. But your formula for the red cells ( =IFERROR(DATEDIF(TODAY(),A1,"m")<1,0) ) doesn't do anything. any thoughts?

**CODO69** · 04-29-2009, 03:33 PM

Ok, I just tried one last thing conneXion. I remembered your 1st formulas you gave me (that you said you made a right mess of those formulas) worked. The one for the red cells. So i tweaked it slightly because your original formula ( =IFERROR(DATEDIF(A1,TODAY(),"m"),0)<1 ) did the same as the new yellow block formula you made me. But instead i changed it to ( =IFERROR(DATEDIF(Q3,TODAY(),"m"),1)<1 ) and it all seems to work swimmingly. So I appreciate all of your help. By the way, how/where did you learn how to do formulas like that? I've tried searching the net and came across exceltip.com, but even then some of the coding is hard to understand. Now only if I can figure out a formula for blocks with dates that are past due I'll be all set!

**CODO69** · 04-29-2009, 03:46 PM

I figured it out for the past due blocks. was tinkering around with one of the formulas you gave me and a little research on the net (=IFERROR(DATEDIF(Q3,TODAY(),"ym"),0) ). Thanks again

**CODO69** · 05-01-2009, 06:37 AM

Let me start out by saying GRRRRRrrrr! Just when I thought it was all figured out, we start a new month and the formulas are not working properly anymore. everything was apparently set for april and worked fine. Now that it's May, the yellow blocks for the next month Don't appear. The red blocks seem to still be working fine along with my over due blocks. I've attached a copy of my spread sheet for anyone who can give me a hand. Thank you.