Date Functions & Formatting

[goskerz]

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.

 

[Ron Rosenfeld]

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,TrainingRequirements,2,FALSE))>=TODAY())

Use for Format 2 (red):
=AND(LEN(B2)>0,EDATE(B2,VLOOKUP(B$1,TrainingRequirements,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

 

[goskerz]

Sir, I have a very good feeling about this. I think it's going to work, just
having a little bit of trouble... For the conditional formatting formulas,
where do I type those in? I went to manage rules, new rule then "use a
formula to determine which cells to format" I copied your first formula in
there but recieved an error message. I am running office 2007. I wish I was a
little smarter on Excel, but I'm most likely doing something wrong. Thank you
for your help!

 

[Ron Rosenfeld]

Sir, I have a very good feeling about this. I think it's going to work, just
having a little bit of trouble... For the conditional formatting formulas,
where do I type those in? I went to manage rules, new rule then "use a
formula to determine which cells to format" I copied your first formula in
there but recieved an error message. I am running office 2007. I wish I was a
little smarter on Excel, but I'm most likely doing something wrong. Thank you
for your help!

Having 2007 makes things a bit easier.

What was the error message?

Did you properly NAME the lookup table?

What, exactly, is the formula? (Copy/Paste it here please).
--ron

 

[goskerz]

I had the table embedded in a different sheet in the document. Moved it over
to the same sheet as the rest of my work and it worked. Thank you, Sir.

 

[Ron Rosenfeld]

I had the table embedded in a different sheet in the document. Moved it over
to the same sheet as the rest of my work and it worked. Thank you, Sir.

Yup. That is one of the limitations of the formulas used for conditional
formatting -- they must refer to the same sheet (there are other limitations,
too).

Glad you've got it working. Thanks for the feedback.
--ron

 

[Gord Dibben]

Ron

You can refer to another sheet for CF if you define a name for cell on other
sheet.

i.e. defined name "apple" refers to Sheet2A1

On Sheet1 in B2 CF>Formula is: =B2<>apple

Also can use use other workbooks if in VBE you add reference to other
workbook.

I have many UDF's in an add-in.

I set my workbook to reference the add-in and I can use UDF's from that
add-in in my CF formulas.


Gord