Calculation in a Rota - Help Needed Please

[NMGIBBS]

I have a monthly rota in excel, that has quite a few different shifts.

I need the total number of hours to be calculated for each person but I don't have a great knowledge of Excel so finding this difficult to get it to work.

I have a sheet with the rota and then a sheet with a list of the different shifts and the number of hours each shift totals. I just need the calculation to appear in a column on the rota.

Is there anyone that can help? Even if I can email the rota to that person in order to show what I mean??

Any help would be greatly appreciated.

Thank you

[mrice]

Suggest that you attach a copy of the rota to your post. Click on Go Advanced to add an attachment.

[NMGIBBS]

Thank you, I have attached this now

[mrice]

Try this user defined function.

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a CU7, enter =TotalHours(B7:AJ7) and copy down.

Remember to save the workbook as a macro enabled workbook .xlsm

[persecuted]

This may work as a non-macro solution since I know not everyone is comfortable with macros. I'm not sure if this gives an accurate answer because the definition for "9to5", "5HR", "E/6" and a lot more are not defined in your second sheet. If you complete that table then this equation for Cell CU7 on Jan 2015 tab should count the occurrences of each entry defined on your second tab and add them together the correct number of times

Please Login or Register  to view this content.

[NMGIBBS]

Thats great, thank you!