Working bar rota with breaks and hours calculated

[jimbob9568]

Hi guys, I come to you seeking much help.

I have gone way beyond my excel knowledge and have now been landed with a task which I cannot do!

I want to create a Staff Rota which can calculate the following

Hours worked in shift, hours worked in day
also needs to deduct breaks upto 4hours =15mins, upto 7hours =30min, over 10hours= 1hr

needs to ignore 'HOL' & 'OFF)
F (which means finish) should be treated as 01:00 (we dont put that otherwise staff think they can just stop @1am wether ithe work is finished or not)

This is the tricky part

is there anyway I can then get excel to map out the times on another chart which shows number of staff working during different times.

typical person rota at present looks like this
Mon Tues Weds Thurs Fri
Adam 6-4 9.5 Off 0.00 Off 0.00 Hol 0.00 14:00 -f 11.0


the first number (6:00-16:00) is the shift the next number is hours worked

If anyone can help... and I know i'm being cheeky with this one, it would be most appreciated.

 

[jmcqup]

This is bloody complicated. Sounds more like a database than an excel problem...

Since it sounds like you can figure out the data, I think what you are looking for is a pivot table. If you give a column/row with the day of the week, you should, without too much trouble, be able to make a pivot table from your dataset with the column heads Staff name, M-F and the data you calculated for the contents. You will obviously run into a problem that Monday occurs about 52 times in a year, but hopefully it sets you on the right track.

hth -j

 

[Toonies]

Hi JimBob

i have a rota that might fit the bill for you.

P.M me and I will email it to you

Cheers

Toonies