Vacation Time formula

P

pgarcia

Hello all,
Do you know of a formula what will let me know when have cumulated 40hr or
vacation time?

e.g. the list of hours

36.96
39.27
41.58 <-- 40hr of Vac time
43.89
46.2
48.51
50.82
53.13
55.44
57.75
60.06
62.37
64.68
66.99
69.3
71.61
73.92
76.23
78.54
80.85 <-- 40hr of Vac time
83.16
85.47
87.78
90.09
 
J

JLatham

Assuming that your first entry, 36.96, is in cell A2. Then in another cell
on row 3 (as B3) put this formula and fill it down:
=IF(MOD(A3,40)<MOD(A2,40),"Added 40 Hours","")
See if that's what you are after.
 
P

pgarcia

That's cool. How does it work? Pleaes

JLatham said:
Assuming that your first entry, 36.96, is in cell A2. Then in another cell
on row 3 (as B3) put this formula and fill it down:
=IF(MOD(A3,40)<MOD(A2,40),"Added 40 Hours","")
See if that's what you are after.
Click to expand...
 
J

JLatham

MOD(number,divisor) takes a value (number) and divides it by the divisor and
shows the remainder. As the value of accrued leave time increases, it has a
larger and larger remainder UNTIL it hits a value evenly divisible by divisor
(40) at which time it will drop.

So if you typed =MOD(78.54,40) the result would be 38.54, but at the next
entry =MOD(80.85,40) the result would be .85. So this drop in the value of
the remainder is what it keys off of: if the test for the current row has a
result smaller than the result of the MOD() of the previous row, we assume
you just passed a new 40 hour point (40,80,120,160, etc).
 
Top