Hi all,
I have been trying to complete a Task on the calculation of vacation entitlement, but now I am stuck and I was hoping someone here would know how to complete this. Here the Situation:
I have a list of employees and their start Dates. In the first year of their employment (the remainder of that calendar year until 31.12.) they earn 10 days of vacation (pro rated of Course, based on amount of days worked in that time). In the second year (full calendar year after their start date from 1.1. - 31.12.) they earn 15 days and in the third (and more) year (calendar year 1.1. - 31.12) they earn 20 days. This means, eg. if someone starts on November 1st, for the first 2 months of their employment, they earn vacation based on 10 days entitlement (10/12*2). Then from the 1st January until the end of that next year, they will earn 15 days and the years after that, they will earn 20 days per calendar year.
So what Formula is there to tell Excel, to Count the remainder of the start year on 10 days, the next calendar year on 15 and all future calendar years on 20 days??
What I Need is, a Formula in column D, that is showing me what their full year entitlement is as of today and in column E, what the total entitlement is since start date. Columns F, G, H, I and J should Show me their entitlement for each year (pro rated if it is in the first year or the current year)
I hope this is not too confusing, but not sure how to explain it better.
Thanks so much for any help you can give me!!