Simple rostering total hours

Copper Contributor

Hi, I am in a new job position where I need to roster shiftwork staff. I barely know anything about Excel but I am trying to help out in the position. I would like to improve the Excel Spreadsheet they are using to automatically calculate how many hours each staff member is doing each month. Is there a way I can get a Letter Combination to automatically depict a number when it comes to tallying the month?



AM = 8.5hrs, DO = 0 hrs, PM = 8.5hrs, ND = 10hrs

And in last box I would like to enter a formula to get total whole time at work automatically, in hours

So spreadsheet would look something like this:


            M    T     W    Th    F     S    S      M    T       Total 

Sam:   AM, AM, AM, PM, DO, DO, PM, ND, ND      62.5


I know it should be possible, but I don't know what terminology to use to be able to research it. Thank you for your help.

1 Reply


A lookup of the codes seems appropriate, but even that technique allows for multiple options (VLOOKUP, XLOOKUP, MATCH and INDEX). See the attached workbook for some ideas.


Of course, the lookup array (a range, as in the workbook, or an Excel table) does not have to be located on the same worksheet.