Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
1  
Is rota short for rotation, or does it have another specific meaning? –  dav Sep 29 '12 at 1:54

2 Answers 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

    RotaShifts

  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.

    Rota_pivot

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.