Vacation entitlement calculation

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!