Employee Leave Calculation

Looking for some help:

Employee accumulates 2.5 days per 30 days (1 month) on the job however no leave is accumulated until 270 days (9 months) from start date and max accumulation is 30 days.

day 1 up to day 269 = 0 leave
day 270 = awarded 22.5 days or 180 hours
every 30 days past the 270th day they earn 2.5 days or 20 hours
max accumulation is 30 days or 240 hours

Thanks for the assistance

Try

Formula:

=MIN(30,2.5*QUOTIENT(C1,30)*(C1>=270))

where C1 is the days from started date.

Originally Posted by José Augusto

Try

Formula:

=MIN(30,2.5*QUOTIENT(C1,30)*(C1>=270))

where C1 is the days from started date.

With this formula, no matter the input in C3 the result is 30.

I use =(YEAR(NOW())-YEAR(E3))*12+MONTH(NOW())-MONTH(E3) to find the # of months from start date

start date is in E3 result is in C17

it works with =MIN(30,IF(H3<8,0,IF(H3>8,H3*2.5,0))) but I would rather it count actual days and not the months.

Or try ...

=MIN(30,IF(TODAY()-E3<270,0,INT((TODAY()-E3)/30)*2.5))

Originally Posted by Phuocam

Or try ...

=MIN(30,IF(TODAY()-E3<270,0,INT((TODAY()-E3)/30)*2.5))

That's Got it! Thanks

Originally Posted by Phuocam

Or try ...

=MIN(30,IF(TODAY()-E3<270,0,INT((TODAY()-E3)/30)*2.5))

This formula is equivalent to =MIN(30,2.5*QUOTIENT(C1,30)*(C1>=270)) where C1 is TODAY()-E3 as I say in post #2
So you can use =MIN(30,2.5*QUOTIENT(TODAY()-E3,30)*(TODAY()-E3>=270))

Formula:

=MIN(30,2.5*QUOTIENT(TODAY()-E3,30)*(TODAY()-E3>=270))

You're welcome!