Reminder notification for training

[femfrog]

Hi,

I was asked to create a spreadsheet to keep track of training for employees.
The employees have to complete a training session every six months. The
training dates are not the same.

For example:

- Employee 1 completed training on Jan 1, 2009 and his next training session
is due on July 1, 2009.

- Employee 2 completed training on Feb 1, 2009 and his next training session
is due on August 1, 2009.

I envision three columns:

1) Employee Name
2) Date Training Completed
3) Next Training Date

Columns 1 and 2 would be inputted manually, and the next training date would
automatically be inputted. For example, I would input that John Doe completed
his training on Jan 1, 2009 and then the next training date, July 1, 2009,
would automatically be inputted.

There are actually three different training sessions that have different
"due dates" . I'm just using 6 months as an easy example. I'm assuming that
the formula used allows me to pick and choose the period of time that the
next "training session" is "due".

The second item I would like to see on this spreadsheet is some kind of
reminder 30 days before the next training date. It could either be a pop-up,
the employees name could appear in another cell with the next training date,
or the respective employees row/cells can be highlighted to indicate that
their training is due in 30 days.

I hope this doesn't sound too complicated. I am using Excel 2001.

I am not a beginner when it comes to Excel, but I am when it comes to
formulas so I would really appreciate if you could simplify your answers for
me.

Thank you in advance for your help.

 

[Max]

Assuming these 2 cols are in cols B and C, data from row2 down

2) Date Training Completed
3) Next Training Date

If B2 contains a real date
and C2 is to output a "6 months" future date based on B2
you could have this in C2:
=IF(B2="","",DATE(YEAR(B2),MONTH(B2)+6,DAY(B2)))
Format C2 as date

If durations (ie the 6) would vary from row to row, easier to use another
col to indicate the duration, and have the expression point to it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[Jon]

Hello!

39850 was a beautiful day here in New Jersey!

Any date in Excel can be converted to a numeric value. 39850 is today's
date, converted to a number by Excel.

Try it. Punch in any date in to a cell, then format the cell as a number
(right click in the cell, then "Format" from the right click menu.)

When you enter a date in cell A1 for instance, you can create a date 60 days
in the future in A2 by creating a formula,

=A1+60

So, that's it.

If you want your fouth column, an alert that training is nearing, you'll
need an IF statement, NOW() and conditional formating.

Column A is the Names.
Column B is the Date of Training
Column C is the result of an addition formula adding X days to data in
column B.
Column D is the result of an IF formula, comparing date in column C to
today's date, and determining if it is less than 30 days difference.

The formula:

=IF((C2-(NOW()))>30,"Training Ok","Training Due in Less than 30 Days")

Then apply a conditional format to turn any cell that contains, "Training
Due in Less than 30 Days" red (or blue, or white with mauve text, whatever).

Let me know!