Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Staff rota, how can I make a letter represent a shift?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Staff rota, how can I make a letter represent a shift?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Staff rota, how can I make a letter represent a shift?
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Staff rota, how can I make a letter represent a shift?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make a letter in a cell automatically equal a dollar amount | Excel Discussion (Misc queries) | |||
What function can make cells shift up when they are blank? | Excel Worksheet Functions | |||
Excel: How do I type a letter in a column and make a check mark a. | Excel Worksheet Functions | |||
How can I make cell data automatically shift up when deleting | Excel Discussion (Misc queries) | |||
How can I make cell data automatically shift up when deleting... | Excel Worksheet Functions |