Mre than your avg conditional formatting with dates...

JPW

New Member
Joined
Aug 15, 2007
Messages
1
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!
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,976
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
Do evaluation dates have years, or not?

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

JPW

New Member
Joined
Aug 15, 2007
Messages
1

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Aug 15, 2007
Messages
1
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top