Hi guys,
I'm looking for some help with a formula, recently I have been promoted to dept. manager in a supermarket this requires me to create weekly rotas for my dept. on MS Excel.
The weekly rotas are pinned on the notice boards for colleagues to read, but also totals up colleague hours per day and per week, and also totals up the hours used on the dept. for a particular day (for management purposes).
Currently, I am required to work out how many hours each colleague works per day, enter a value in the "Hrs" column per shift, per colleague and theres a sum formula to total these up, providing the total number of hours a colleague
has worked in the week (To ensure colleague is working contracted hours) (I have attached the new rota I have been working on - its relatively easy to understand looking at that)
Ideally I would like a formula to work out the duration between two times (ie. a shift 07.00 - 12.00 = 5 Hours), and IF the shift exceeds 6hours to minus a 30min break (Ie. a shift 12.00 - 20.30 = 8hours) because the colleague
clocks out for a 30minute break, hence does not get paid for that break period.
I have been trying to work out a formula for hours, but no luck
Colleague D is highlighted Orange as it contains no formulas/formatting etc. and ideally this is what I would like the entire rota to look like.
Sorry if I'm asking a lot, just wanted to make my life easier and save time, while minimising mistakes.
Any Help would be greatly appreciated
Regards,
Matt
Bookmarks