Help please

[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

 

[Guest]

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.

 

[Peo Sjoblom]

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"

 

[srhyllnd]

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.

 

[Peo Sjoblom]

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

 

[srhyllnd]

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.