I need some help finalizing a Paid Time Off (PTO) tracking spreadsheet for my little business. Here are the assumptions. My "employees" need to have worked for six months to qualify for 5 days of PTO - time they must use before the end of their first year (so they get nothing for the first six months but the first day of month seven they will receive a lump-sum of 5 days of PTO that they have to use before the first year anniversary of their start date). From year one to year two, they qualify for 5 days of PTO as well. You can see that the time they qualify for increases with the duration of their employment (2-4 years they get 8 days; 5 years plus they get 12).
My problem is that I would like for the PTO they qualify for to reset to the appropriate number of PTO days they qualify for at the end of each calendar year. However, a calendar year is different for each employee because they all have different start times. For example, employee #1 (A2) started September 15th of this year while employee #2 (A3) started January 1st 2012. As a result, their respective 6 month, 1 year, 2 year, and 5 year anniversaries will occur on different dates.
I would just like for the Qualified PTO to automatically refresh at the appropriate time for each employee. Then all I'd have to do is input the PTO Taken amounts as they occur and it will be an accurate reflection of how much time they have left. The reminder column I have on the far right is my lame attempt to create a field that will set an alarm so that the anniversary dates can be changed manually. Any help anyone might have would be greatly appreciated by this excel novice. Many thanks
Bookmarks