I have a list of dates when my company offers specific training that all of my new employees go through once they've been with us for 30 days, 60 days, and 90 days. I want to set up a spreadsheet where I can input the employee and their start date and have the spreadsheet assign them to the date of the class they need to attend.
I have the list of training dates in one area of the spreadsheet, and then the employees and their start dates in another. I need to figure out how to somehow put together a formula that will take their start date, and then for the 30 day training find the closest training date after they have completed at least 20 days of work. And for the 60 day I need for it to take their start date and find the closest training date from the list once they have completed 50 days of work. The same for the 90 day training, I need for it to figure the closest date to 80 days plus their hire date.
if John Doe was employed on January 9, 2013, and the dates I have listed are:
1/6/2013, 2/3/2013, 3/6/2013, and 4/9/2013.
For John I would want it to pick the 2/3/2013 because it is the closest to 20 days after his start date.
But if I have Jane Doe who started on January 23rd, 2013, I would want for it to return 3/6/2013 and skip the 2/3/2013 because she would not have completed 20 days of work yet.
Does anyone know how I might put that together?
Thanks in advance for any help.
Bookmarks