Yitzhack,
That would require you to know what columns the days were in. If you only
had 5 columns, it could be done, but with L:BF that makes it much more
difficult. Secondly, you would also have to have some logic into each
person, or a variable-based function. For the variable based function, you
would have to create a cell for each different criteria. For example: BG9
would have to contain Mondays normal total hours, BH9 Tuesdays normal total
hours, etc... as well as say AA9 to have your criteria to display half days,
say 3 hours or something. It's not IMPOSSIBLE, but much harder to do.
Another possibility would be to create another table for Normal total work
hours and half day hours and place a VLOOKUP against the person's name for
the number that you were looking for and change the coding as such:
=SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<>""))+SUMPRODUCT(--((L9:BF9)>0),--((L9:BF9<VLOOKUP(name,table_array,column_number,FALSE))))+(SUMPRODUCT(--((L9:BF9)>=VLOOKUP(name,table_array,column_number,FALSE)),--((L9:BF9)<VLOOKUP(name,table_array,column_number,FALSE)))*0.5)
This is very hard to accomplish correctly, but not impossible if you spend a
little time on it.
--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.
Yitzhack said:
Thomas, Thank you so much. This is definetely what i needed.. Do you think
that the formula for the "part-timers" is something doable?
Thanks again
Regards
YM
:
Yitzhack,
Ok, seems like I have what you need. I think.
Basic Parameters:
<cell>=0 then 1
<cell>>0 and <5 then 1
<cell>>=5 and <8 then .5
<cell>=8 then 0
=SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<>""))+SUMPRODUCT(--((L9:BF9)>0),--((L9:BF9<5)))+(SUMPRODUCT(--((L9:BF9)>=5),--((L9:BF9)<8))*0.5)
--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.
:
what it needds to return is if 0 = 1 the current formula returns if > then 5
= .5.
BAsicaly what i need is if L9 is 0 = 1 if is >than 0 but< than5 = 1 if >than
5 but <than 8 = .05.
If you miss up to 3 hours out of 8 = .05
if you miss full day = 1
If you miss more than 3 = 1
--
Regards
YM
:
Yitzhack,
Could you give me the example data? L9:BF9
Secondly, what is it that you would like to happen? Would you just like to
include 0 into the formula? You could change that with ">=0" in the first
COUNTIF. I could be mistaken. In all essence, what would you like the
formula to return?
--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.
:
Good morning all. I'm currently working on a attendance tracker, I have a
formula that will give us either half or full occurrence. Before we didn’t
need to track 0 but now we have. I tried to change the formula to give us a
full occurrence (1) but I’m getting an error. Can anyone help me with this…
Thanks
=(COUNTIF(L9:BF9,">0")-COUNTIF(L9:BF9,">=5"))+((COUNTIF(L9:BF9,">=5")-COUNTIF(L9:BF9,">=8"))/2)
