Hey guys,

I have tried a formula to count the sick leave taken but i realize
that my formula is wrong b in my company the balance of my sick leave
will be restarted each time the employee completes the year so i
messed up


please help this is the formula that i have used


C=Starting Date


and this is the sick leave schedule :
3months to 2 years:15 days
2 years tp 4 years :30 days
4 years to 6 years: 45 days
6 years to 10 years : 60 days


=IF(TODAY()-C66*365,60,IF(TODAY()-C64*365,45,IF(TODAY()-
C62*365,30,IF(TODAY()-C60.25*365,15))))


please advise

Try this

=IF(DATEDIF(C6,TODAY(),"M")<3,0,LOOKUP(DATEDIF(C6, TODAY(),"Y"),{0,2,4,6},{15,30,45,60}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tia" wrote in message
...
Hey guys,

I have tried a formula to count the sick leave taken but i realize
that my formula is wrong b in my company the balance of my sick leave
will be restarted each time the employee completes the year so i
messed up


please help this is the formula that i have used


C=Starting Date


and this is the sick leave schedule :
3months to 2 years:15 days
2 years tp 4 years :30 days
4 years to 6 years: 45 days
6 years to 10 years : 60 days


=IF(TODAY()-C66*365,60,IF(TODAY()-C64*365,45,IF(TODAY()-
C62*365,30,IF(TODAY()-C60.25*365,15))))


please advise

=LOOKUP(DATEDIF(C6,TODAY(),"m"),{0,3,25,49,73},{0, 15,30,45,60})


"Tia" wrote:

Hey guys,

I have tried a formula to count the sick leave taken but i realize
that my formula is wrong b in my company the balance of my sick leave
will be restarted each time the employee completes the year so i
messed up


please help this is the formula that i have used


C=Starting Date


and this is the sick leave schedule :
3months to 2 years:15 days
2 years tp 4 years :30 days
4 years to 6 years: 45 days
6 years to 10 years : 60 days


=IF(TODAY()-C66*365,60,IF(TODAY()-C64*365,45,IF(TODAY()-
C62*365,30,IF(TODAY()-C60.25*365,15))))


please advise