Spliting Day Shift and Night Shift Hours

**Goldbadger2** · 04-25-2013, 11:24 AM

Hello,

On the face of it my problem seems simple, but gets complicted because of the 24hour time system.

Assuming I have a start time in A1 and a finish time in A2 I can easily work out the total in between.

For example Production Start 23:00 Production Finish 08:00 - Total time 9 hours - so far so good.

Now Assume that I have two shifts, Day shift (6am to 6pm) and night shift (6pm to 6am)

Night shift started production at 23:00 and they hand over to day shift at 6am so..

Night shift has 7 hours of production and day shift has 2 hours - 9 in total.
A1 - A2 - A3 - A4 - A5
23:00 - 08:00 - 9:00 - 7:00 - 2:00

Production times can start at ANY time but shift patterns are fixed.
What formula can I use in cells A4 and A5? Or will I need VBA for this task?

Thanks for reading.

I'm guessing this is very hard to achieve?

**Goldbadger2** · 04-26-2013, 06:05 AM

Bumping this post.

**kvsrinivasamurthy** · 04-26-2013, 08:18 AM

See the attached file with formula.

**Goldbadger2** · 04-26-2013, 08:41 AM

kvsrinivasamurthy, thank you for the formula it works a treat!

I have tried to break your formula with all kinds of times and so far it has stood strong.

Kudos to you