Mre than your avg conditional formatting with dates...

[JPW]

I work with the engineering department of a rather large company, and some of the spreadsheets they use around here seem pretty inefficient in either practical or ink use; in an effort to improve this, I have been redesigning all of the departmental spreadsheets. I have had a few basic classes in Excel (more than anyone else around here) so I get things set up with formulas, etc., but I have never had reason to use conditional formatting before, and after a couple of hours of searching the web my answer to this eludes me.

Here's my problem: I have a spreadsheet containing the entire department's position titles, names of the people in those positions, hire date, evaluation dates, etc. Currently we are reading down the evaluation date column to determine who is due for an evaluation every month. What I would like to do is apply conditional formatting (without adding another column for next evaluation date, since these occur every 12 months to the day pretty much) which would apply formatting to a cell if the date within the evaluation date field is one year from the last evaluation date.

What I'm aiming for is a formula which highlights the field green, let's say, if it's within 15-30 days before the employee's next review; yellow when it is within 1-14 days before the review; and red text when it is the day of, or any day after, the review date.

I've found many similar formatting tips, many of which I have tried to modify to that purpose and Excel has kicked them all back at me; any help you have in this would be very much appreciated. I'm sure that, if I can get this figured out and move on to correct and improve other spreadsheets, I'll be visiting this forum a few more times. Thank you!

 

[mikerickson]

I assume you are using custom formulas for the condition.

=(DAY(TODAY())+30>DAY($A$2))

will highlight dates within the next 30 days.

 

[barry houdini]

If last evaluation date is in A2 and you want to colour B2 when the next evaluation date is approaching. Select B2 and use these conditions with Conditional formatting

Condition 1 - red

=(DATEDIF(A2,TODAY(),"y")>0)*(A2<>"")

Condition 2 - yellow

=(TODAY()>A2+350)*(A2<>"")

Condition 3 - green

=(TODAY()>A2+335)*(A2<>"")

 

[xenou]

Do evaluation dates have years, or not?

I.e. Hire date: 7/1/2004 Evaluation date 9/15

 

[JPW]

Yes, we do include the year; of course, Excel seems to automatically add this in for the current year, even if you only type 9/15 for example. It seems, though, that they have not been updating the years on people who have stayed in the same position for several years (your review date is based on the date of your last promotion) so I have some entries saying people were due for reviews as far back as Feb 06.

Barry's formula seemed to work in most respects -- but it only seems to highlight randomly. Feb 27, 06 turned red as it should, however on the next row Aug 9, 07 didn't change at all. Since this spreadsheet has not been updated for years in quite awhile, most of these should be red, another few should be either yellow or green, and none of them should be without some sort of formatting change...until I go back through and research the actual last review dates for these guys. However what I'm finding is that most of these retained the original (simple black text) default formatting.

If it helps at all, the column all this is in is G, starting with row 12 (the previous rows have administrative personell, for whom the review process is a little different and doesn't really need to be formatted).

Thank you

 

[barry houdini]

however on the next row Aug 9, 07 didn't change at all.

Is this the date of the last review? According to your criteria that date wouldn't be highlighted until 30 days before Aug 9, 08. Or am I misunderstanding?

 

[JPW]

Currently the column's dates have not been updated with the employee's last review. This is something that I am going to have to go through and update later -- right now they are almost all 2006 or earlier (and should be highlighted red, since as far as the spreadsheet knows, the dates have passed. Once today's date is within 1-14 days of being 1 year later (i.e., 351 - 364 days after the date in the column) it should pop up with the yellow conditional formatting. 15-30 days (335 - 350 days after last review) would turn it green.

Of course, now that I'm looking at it, this formula might actually be working better than my brain...they've changed the requested functionality of this spreadsheet several times, and recently even mentioned merging it with another spreadsheet, so I may just be imagining things...yeah, so far the last few rows have actually been right. It seems that, while they aren't updating the years, I'm not paying attention to them either. Hah! That could be the source of my confusion...if I have any more problems, I'll add on to this. Thank you