Forum Discussion

KirSipp's avatar
KirSipp
Copper Contributor
Mar 04, 2024

Simple rostering total hours

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?

 

E.g

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.

excel
Formulas and Functions
  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    Mar 04, 2024

    KirSipp 

    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.

     

Resources