Help please

  • Thread starter Thread starter srhyllnd
  • Start date Start date
S

srhyllnd

I was wondering if anyone could possibly help me. I'm trying to pu
together for a large staff roster (timesheet)

I have the names and dates etc on. We have lets on days that giv
details of the shifts i.e. L = 2.30-11.30pm, Z=6.30pm-11.30pm etc. No
I have the formula of countif to count up how many people are in eac
shift etc but I would like a formula that would change go next to thes
where it would find that if L is in a column it means 8 hours (minu
lunch) and Z = 5 that way I can work out how many hours people ar
doing. Is there any way at all that I can do.

Does this make any sense at all
 
you seem to be making it harder for yourself than need
be. It would be better to put in actual hours worked....8
or 5 and then total them.
 
One way

=SUMPRODUCT(COUNTIF(B2:B200,{"L";"Z"}),{8;5})

for a total for both, if you only want to sum L

=SUMPRODUCT(COUNTIF(B2:B200,"L"),8)

do the same for "Z"
 
Thanks for your help but neither are what I'm looking for. I have a
large staff force to try to rota for a month. We have the codes so
that we can count how many people roughly are on a shift. I wanted
something that when I put "C" into one column in the next (hours) it
would put 5 then I could auto calculate the hours rather than having to
do it manually everytime and taking up even more time than these things
take.

Thanks for trying though, I think it's time to invest in a specific
programme for doing these.
 
No, you can do that easily in excel, just make a table like

A B

C 5
L 8
Z 6
etc then use vlookup in the cell next to where you put the letter, if you
put the letter
in A2 in B2 put

=IF(A2="","",VLOOKUP(A2,{"C",5;"L",8;"Z",6},2,0))

copy down as long as needed, edit the letter and numbers so they are
correct,
quote s around the letter, then a comma and the hour value, then semicolon
and next letter
 
I just wanted to reply with a great big, HUGE thank you for your help.
The formula worked wonders and I've now got 3 months rota's done in
just under a week rather than having to spend nearly a week on one
week. THANK YOU! I got a praise from my boss and my general boss on it
all. They also want to use the timesheet I created with you're
wonderful formula as a template through out the company and in the
different offices. THANK YOU! I can't say how much that one forumla
helped me.
 
Back
Top