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

Answer 1

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.

Answer 2

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.