Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sick time usage
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sick time usage
Try this:
=IF(B10="*",AF8+10.64,AF8)-(SUM(COUNTIF(B1:K1,{"*SE*","*SF*"}))*24) -- Biff Microsoft Excel MVP "ken" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sick time usage
P.S.
Change the range to suit in the COUNTIF function! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(B10="*",AF8+10.64,AF8)-(SUM(COUNTIF(B1:K1,{"*SE*","*SF*"}))*24) -- Biff Microsoft Excel MVP "ken" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sick time usage
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" wrote: P.S. Change the range to suit in the COUNTIF function! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(B10="*",AF8+10.64,AF8)-(SUM(COUNTIF(B1:K1,{"*SE*","*SF*"}))*24) -- Biff Microsoft Excel MVP "ken" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sick time usage
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. -- Biff Microsoft Excel MVP "ken" wrote in message ... 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" wrote: P.S. Change the range to suit in the COUNTIF function! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(B10="*",AF8+10.64,AF8)-(SUM(COUNTIF(B1:K1,{"*SE*","*SF*"}))*24) -- Biff Microsoft Excel MVP "ken" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sick Time Reports | Excel Worksheet Functions | |||
Tracking accrued sick time with excel | Excel Discussion (Misc queries) | |||
VACATION/SICK TIME ACCRUAL | Excel Discussion (Misc queries) | |||
Sick time accumulation | Excel Worksheet Functions | |||
template to track sick and vacation time for a year | Charts and Charting in Excel |