sick time usage

[ken]

I have created a time card program for work. we work 24 hour periods. if the
member calls in sick for the entire shift I have figured out. what i can not
figure out is how to get the formula to recognize how many hours of sick time
are being used for the day if not equal to 24 hours. example: if the block
holds "SE/11" then the member was sick for 13 hours and worked 11 hours. the
block my even read "8/SE", where the individual worked 8 hours and was sick
for 16 hours. I am not sure how to get that formula to recognize the SE in
this instance?

THE FORMULA IS:

=IF(B10="*",AF8+10.64,AF8)-(COUNTIF(C11:AD11,"SE")*24+COUNTIF(C11:AD11,"SF")*24)

The (B10="*",AF8+10.64,AF8) part of the formula is not relevant to
calculation of sick time other than bringing down the sick time balance from
the previous period. If the entire worksheet is needed i can provide it and
the formula. thanks again for any help possible

 

[T. Valko]

Try this:

=IF(B10="*",AF8+10.64,AF8)-(SUM(COUNTIF(B1:K1,{"*SE*","*SF*"}))*24)

 

[T. Valko]

P.S.

Change the range to suit in the COUNTIF function!

 

[ken]

hi thanks for the help. that gets me to see a se and sf either way. i have
a question though. i need to figure what se or sf is in relation to the
number of hours taken. it may not always be 24 like the formula. can i
modify this to figure how many hours are used ranging from 1 to 24? this
time it maybe "se/10" where se = 14 hours and the next time it maybe "sf/16"
where as sf = 8. the 24 in the function makes it 24 hours all the time is
that right? can you use a len or mid function to subtract the hours used
from 24. example ....*(24-len(A1:A10),-3,0). i do not know if this will
work. and if it does it will not figure if se or sf is in the second part of
the equation ie "19/sf"

 

[T. Valko]

To make your life easier you need to come up with a "standard" method of
entry.

SF/10
10/SF

Choose one or the other but not both. Once you have a standard then you
should be able to do what you want although that still makes things more
complicated than need be. As a general "best practices" rule, you shouldn't
mix text with numbers and then expect to use those numbers in calculations
without some amount of difficulty.