Employee Leave Calculation

Hi all!

I've been having difficulty finding leave formulas for our employees.

Every month upon date hired, EE will get 1.25 leave credits til end of year. So they'll get maximum of 15 leave credits in a year. After a year, they'll be reimbursed max 5 days worth of leave credits and the remaining will be used on the next year while still earning credits.

Example:

I was hired April 4, 2019. Upon December 2019, I'll have 9(months employed for year 2019) x 1.25 = 11.25 leave credits.

Come my 1st year of employment, April 4, 2020, the management would first convert the 2019 leave credits (11.25) so if I didnt use them up, id still have 6.25 left.

January 2020 up to April 2020, I accumulated 5 credits plus 6.25 from 2019 so I should have 11.25 as of this date.

I find it difficult to have a clean spreadsheet for this as I'm just following the format of what I have since I got in the company and there were already a lot of formula changes in the file. Also, since the excel is too heavy to save all data of absences, I only have record of paid and unpaid leaves since 2019.

Please see attached file for reference.


Thank you so much!!!!!!