Help! calculating hours worked using a rota

[Mark G]

I have recently been given the work rota to take charge of and I am shocked that to this point everything is being manually input and worked out!

what I have:
an Excel sheet with a rota of days, list of venues on each day and which staff work there. staff move venues during the day, so may work at venue A for 5 hours and then venue B for 3 hours.

what I need:
a sheet that tells me how many hours each individual staff member has worked - adding up their hours accross the 5 days at the different venues they work. The issue is, that staff may change venue at last minute, so when I move their name from Venue A to venue B, the sheet will have to recognise that they have moved venue and thus their hours worked may have changed.

Is this even possible in Excel??

thank you in advance to anyone who takes the time to read this and help out.

 

[steve the fish]

This sort of stuff is pretty simple as long as you understand various rules. How are your staff to be named? There is a big difference between A. Smith and A.Smith. You need to be totally bulletproof with naming people. Id consider staff numbers to be honest. After that I cant see your sheet. Setting it up properly in the first place would be beneficial rather than having to write convoluted formula.

 

[Mark G]

Hi Steve,

thank you so much for getting back. I have a blank canvas on this one in terms of what to call the staff or whether to number them. Apologies, I should have shown you exactly what I have - its literally one sheet! I have pasted a link to it below. if you imagine, staff will teach PE according the timetable I have filled out, then they will go to an AfterSchool club for an hour or two. some people teach PE on the same day in more than one school. Some people are 'spare' and will cover if someone is ill. Staff who are 'flexi' means they are off that day and owe hours back, which they will do perhaps in the football/dance academy rota. I haven't filled those ones out - I'm not sure whether its easier to fill those other ones out before trying to work all this out, or whether to get someone to show me how to the formulas i need to do to work out the total hours before i input it. hope that makes sense.

https://drive.google.com/open?id=0B2VAdjublOjUcGxlOEYzUkZSbkE

 

[steve the fish]

Oh my. That is not going to do it for you! Its a whole project to sort that out. A few pointers. Use Names not 1) Name. Use true times 10:00 not 10.00. Do not use from to times in the same cell. Use 10:00 in one cell and 14:30 in another cell. Use 24h clock. 14:30 not 2:30. These are the headers I would use:

Teacher Name Lesson Date Time From Time To School

You can then make easy calculations based on school, name, lesson, times or even use pivot tables. As it is at the moment that's impossible.