If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reminder notification for training
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. |
#2
|
|||
|
|||
Reminder notification for training
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 --- |
#3
|
|||
|
|||
Reminder notification for training
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! "femfrog" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|