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.

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

"Mike Ellis" wrote:

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.

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.

"bpeltzer" wrote:

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

"Mike Ellis" wrote:

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.

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.

"bpeltzer" wrote:

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

"Mike Ellis" wrote:

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.