I have a duty rooster that calculates working hours and divides them to 6-14, 14-22, 22-06.



Now, I would like to include hours worked on weekend (Saturday, Sunday)and hollidays.

Problem is, i.e. Friday night shift will go from 23.00 to 07.00 which means, 7 hours will actually be worked on Saturday.

I tried to change the formula to calculate how many hours were worked on Saturday, from midnight on, and add it to the end of table, but failed.

Please help.

Attached Files

Duty_Rooster_sample.xlsx (16.3 KB, 43 views)

What does that mean? Are those regular shifts? Are they paid for at differing rates? I suppose you want formulas to split the hours into these three "categories".

I can't make hide nor hair of your equations in rows 6-9. What is it you want to do exactly?

Are the times always integer hours?

Why does the roster span 10 days?

See attached as a kind of tear down/rebuild to perhaps rethink the idea. Nothing is in stone.

Attached Files

Duty_Rooster_sample.xlsx (13.8 KB, 14 views)