# Complex rota hours calculation in excel

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

-
Is rota short for rotation, or does it have another specific meaning? –  dav Sep 29 '12 at 1:54

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.

-

You could have a table set up with drop down lists so every shift in is in the same format. Then have a table with the shifts and the hours beside them. Like 7-9pm in one cell and then 2 hours in the cell beside it.

Them use a sum vlookup like

``````=SUM(VLOOKUP(B6;Sheet2.A3:B4;2;0);VLOOKUP(B7;Sheet2.A3:B4;2;0))
``````

This will give you the total hours.

Please note i used open office to check this works if using microsoft excel change ";" to "," in the formula. If shedules dont have hours you can either fit in an if statement or you can have a not working 0 hours in the schedule.

-