I'm working on setting up a system for annual leaves. I have a fixed allocation of 13 leaves for each person per year. I'm figuring out how to calculate this so that anyone who joins in any month receives leaves based on the month when their probation period is completed.
Also need to do similar calculations for Casual and Sick Leaves. I've attached a sheet with the results. I've used a straightforward formula, and it's giving results for some rows.But does not work for some of the row.
your help will be much appreciated.
=IFERROR(IF(YEAR(F4)=2024,CEILING(ROUND(($B$2-MONTH(DATEVALUE(TEXT(F4,"mmm")&" 1")))*$B$2/12,2), 0.25),IF(DATEDIF(F4, TODAY(), "M") > 12, $B$2, "Error")))
I believe there's no need to include DATEDIF() in your formula as, based on your description of the issue, anyone whose probation period ended before 2024 should have full annual, Casual, and Sick leave allocations for 2024. I've shortened your formula to the following:
=ARRAYFORMULA(IF(YEAR(F4:F34)=2024, CEILING(ROUND(($B$2-MONTH(F4:F34))*$B$2/12,2), 0.25),$B$2))
Paste the formula above on H4 to compute the annual leave allocation based on the confirmation date on the F column. For the Casual and Sick leaves, replace $B$2 with $B$3 and $B$4 then paste the updated formulas on I4 and J4 respsectively.
Let me know if this works for you. If you're expecting different results, I suggest following @rockinfreakshow's recommendation and manually enter your expected output on the test sheet.