Calculating Sick Days

[Poppachuck]

I am new to all of this.

Here is what I have to work with.

Sick time accrues at a rate of 4.62 hours every other Friday.

( all time in hours and tenths)
A1 is starting date say 1-2-2009
A2 is today’s date via =TODAY()
B1 is sick time total say 104.89 as of today
B2 is sick time used
I need to add 4.62 hours to B1 every other Friday or 14 days with a cap of 1440hours, minus the hours in B2.

Thanks for your help.

[daddylonglegs]

If B1 has this formula

=(INT((A2-A1)/14)+1)*4.62

that will give you a total of 106.26 (which is the equivalent of 4.62 added on the date in A2 and then every 2 weeks). I'm not sure how you get 104.89 is that including some deductions?

Is the cap at 1440 hours before the subtraction of B2 or after? If it's before then B1 should be

=MIN(1440,(INT((A2-A1)/14)+1)*4.62)-B2

or alternatively

=MIN(1440,(INT((A2-A1)/14)+1)*4.62-B2)

[Poppachuck]

104.89 is what this employee has after many years of adding and subtracting hours.
the employee can acrue no more than 1440 hours
Thank you!

[Poppachuck]

1-2-2009
is just a date I started with to calculate from 1-2-2009 point forward.

[daddylonglegs]

OK, does that formula do it for you? You'll have to adjust it, then, to add in the current number of hours......

[Poppachuck]

Wow I think we are close I am not sure how to add that number in? When I do it appears to remove my formula.

[daddylonglegs]

Well if B2 is sick time used you are already subtracting that amount so this formula

=MIN(1440,(INT((A2-A1)/14)+1)*4.62-B2)

should do what you want assuming A1 is the first date on which that employee accrued sick time. If there's any other adjustment you should be able to add or subtract after -B2, e.g.

=MIN(1440,(INT((A2-A1)/14)+1)*4.62-B2+100)

[Poppachuck]

There is the rub, the employee has been accruing sick leave for many years.
he is starting with 104.89 then on the stated date1-2-09 I started adding 4.62 hours every payday not to exceed 1440 hours.

I have perhaps mis-stated slightly.
Thank you for getting me this far.

[Poppachuck]

Think I got it!!!!


=MIN(1440,(INT((A2-A1)/14)+1)*4.62-B2+104.89)