On Sat, 23 Jan 2010 17:21:01 -0800, goskerz
wrote:

I'm putting together a spreadsheet showing personnel recurring training dates
and whether they are overdue or current in that training. I have about 100
people with about 50 different training requirements that are recurring on
different timelines (ex: Mark Smith has to take CPR class every 20 months and
a driving course every 12 months). I'm trying to develop a function/format
combo that lets me type in the date they last had that training, paired with
the required frequency of the training, giving me a result of "CURRENT" or
"OVERDUE" and turning the cell green or red. I've played around with the
TODAY() function a little bit, but I can't seem to figure anything out.

How best to do this may depend, in part, on your version of Excel.

Setup a table as follows:

A2:An Names
B1:xx1 Classes

Set up a lookup table someplace named Training Requirements.

Assuming your requirements can all be expressed in months (or years), list the
Class name in one column, with the number of months in the second column. These
names should match exactly the names in B1:xx1 (row 1)

e.g.

CPR 20
Driving 12

A2: Mark Smith
B1: CPR
C1: Driving

Then in B2:xx2 you will enter the date last taken

For a conditional formatting formula

Select B2
Use for Format 1 (green):
=AND(LEN(B2)0,EDATE(B2,VLOOKUP(B$1,TrainingRequir ements,2,FALSE))=TODAY())

Use for Format 2 (red):
=AND(LEN(B2)0,EDATE(B2,VLOOKUP(B$1,TrainingRequir ements,2,FALSE))<TODAY())

Then copy the formats to the applicable cells.

If you are using a version of Excel prior to 2007, you may get an error message
if you cannot use Analysis ToolPak functions. (I can't recall if they are
allowable in the older versions of Excel.

If that is the case, you will need to set up a matrix of cells someplace, using
the above formula, to generate the actual due dates. In that case, assume you
set this up in AB2:xxnn, then your conditional formatting formula would be, for
B2, something like: =and(len(b2)0,ab2=today()) for the green and
=and(len(b2)0,ab2<today()) for the red

--ron