Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
Is rota short for rotation, or does it have another specific meaning? – dav Sep 29 '12 at 1:54
up vote 1 down vote accepted

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.

    enter image description here

    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.


share|improve this answer
VLOOKUP is not optimal for reasons‌​. The better formula would be =INDEX(Table1[Length],MATCH([@Shift],Table1[Shift],0)) – Engineer Toast Feb 20 '15 at 15:29
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. – David McGowan Jul 12 '15 at 0:55

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


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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .