Staff rota, how can I make a letter represent a shift?

[Guest]

I am trying to set up a staff rota in Excel 2003. Can I enter letters to
represent not only the shifts but also the hours of the shift e.g. E = Early
shift, 8 hours,L=Late shift,7.5 hrs,X=day off, that can then be added
together with other shifts to check total hours worked?e.g
would return total..
M T W Th F S Total
VM L E L E E X 39
NJ E L E L E X 39
BH L L E L E X 38.5
Thanks.

 

[Guest]

Sure. You can calculate the total hours along the lines of
=7.5*(countif(b2:b7,"E")+countif(b2:b7,"L")). I'd suggest using data
validation on each cell with the letters to ensure that the letter entered
corresponds with a letter recognized in the calculation.
--Bruce

 

[Guest]

Many thanks for your help Bruce. One further question, being a novice with
Excel-since the shift hours vary (e.g. E=7.5, L=8, "F(lexi)" =5 etc),where do
I place the sums corresponding to the other shifts?
Mike.

 

[Guest]

Bruce -please ignore my last reply -I think I've got it, thanks to you.
=8*(COUNTIF(B97:H97,"E"))+7.5*(COUNTIF(B97:H97,"L")) seems to work, &
presumably I can just keep adding for each shift variation.