Forum Discussion
New Hire Required Training
Hello Everyone!
Apparently I'm my companies Director of Training and one of my responsibilities is to track New Hire Required Training (i.e. Sexual Harassment, Diversity, Whistleblower and Mandated Reporter). Some of these are once a year, some are only upon hire and two of them are every 3 years.
I'd like to create a spreadsheet that would track the employee, their dept., the title of training along with date and maybe color coordinate things to show when they're about to expire/expired. I would also LOVE to throw in a pie chart showing a company compliance percentage (because who doesn't love a good pie chart). I'd also like for it to be upkeep friendly.
Any direction would be extremely appreciated!
Thanks,
Scott
1 Reply
Scottie-B See the attached workbook.
Of note:
- The meanings of values 365 and 1095 should be obvious. The Next Training date calculations work even if those numbers are not multiples of 365.
- New employees are immediately behind on all training. If you wanted a grace period, you could add a Hire Date column and adjust all the formulas, but that could get messy.
- I used 60 days in the conditional formatting formula that warns of training about to expire. That's one value for all training, but you could create additional conditional formatting that uses a different number of days (and a distinct color, if you wish).
- I formatted the dates to show month and year only; this is not required, but...
- Date arithmetic is inexact (because a year is not always 365 days, and the calculated Next Training dates may fall on a weekend or holiday).
- Rather than including the TODAY() function in many formulas, I use that function only in cell A1. You can temporarily put different dates into A1 to check spreadsheet behavior.
Regarding maintenance:
- You should add employees by copying & pasting plus editing an existing row. Creating a macro to do that work is certainly doable, but then you have a macro-enabled worksheet, with related security concerns.
- Creation of new training requirements should involve copying & inserting & editing one or two columns, changing the formula for calculating Status (currently in column J) and extending that, extending the conditional formatting to include a new column*, and changing the formula in C4 on the second spreadsheet. (Mandated Reporter is left as an exercise for the reader.)
* This is likely to be the most difficult change, as the conditional formatting editor is ... difficult.
