Complex rota hours calculation in excel

Question

OK so there are a number of different shifts in operation here and I want a total hours formula.

problem is the rota is written using a number of different notations for different shifts

some are 2-FIN some are 7-5PM some are EARLY or LATE

now I know what each shift is in terms of hours, but I want a way to get excel to calculate them, now I suppose I could create a table with a load of possible shifts and their total hours and then have excel match the rota to the table and sum total hours for the week

but I have no idea how to do this, all my attempts at this come back as problem with your formula and no further explanation

Solution

Since you're using Excel 2010, you have some great tools to make this easy. Here's what I'd do:

1. Make a simple table for your shifts. It can be as basic as two columns: Name & Length

   

2. Make a table for your schedule. This should have four columns: Date, Employee Name, Shift, Hours.

   

   Date can be entered manually.

   Both Employee Name and Shift can use Excel's Data Validation to create a drop-down list of specific values.

   Hours will use VLOOKUP to determine its value from your shift table. In my example, the formula is =VLOOKUP([@Shift],Table1,2,FALSE). This will lookup the Shift value in your second table in the first table, Table1, and return the second column, Length and will only return an Exact Match (2) (which means you don't have to have things in alphabetical order).

   I also added a total row to the table for a quick check of hours.

3. Finally, add some additional functionality by creating a Pivot Table from your second Table. This will allow you to quickly summarize and report your data. You can make multiple pivots from a data source, so you can make these two easily and you can update them whenever your source data (Table 2) is updated.

   

Comments

[dav]

Is rota short for rotation, or does it have another specific meaning?

[Engineer Toast]

VLOOKUP is not optimal for reasons. The better formula would be =INDEX(Table1[Length],MATCH([@Shift],Table1[Shift],0))

[David McGowan]

I never used this in the end... too much work as the rota is done one per week... but for future reference i know how it works (plus im using both =INDEX and vlookup much more now.