Deduct hours when date & time carry to the next day

Hello, I have a simple userform that has command buttons to enter current date and time "start" & "stop" to capture process times. The users are on 2 work shifts and when the people working at night are working a process that they won't finish, I do not want them to hit "stop". The next morning the day shift people will finish and press "stop". Is there a simple cell formula to deduct the hours in between? For example:

Start: "04/19/2012 8:00 PM" (operator ends shift at 9:30 PM)
Stop: "04/20/2012 6:30 AM" (operator starts shift at 5:00 AM)

What I need is the total time, which in this case should be 3.0 hours.

I am using this formula right now to get me rounded time to the nearest tenth of an hour:

Total time in Column C: =ROUND((+B9-A9)*24,1)

Any help is greatly appreciated.... thank you!

Hi
Maybe something like:

=MOD(StopTime-StartTime,1)*24-MOD(ShiftStart-ShiftEnd,1)*24

This is assuming you cells are formatted to Date & Time as in your example.

Hope this Helps
DBY

Hi DBY,

Thanks for your reply and help..... this does work, but how can I format this to work with my current rounding formula? Also, it seems to fail if the time does not carry over to the next day, it shows a negative number. My typical case is where they start and stop on the same day but if they happen to start a job at night and do not finish, that is where this formula is most needed. Thanks very much for your help!

Hi again
It's a bit of a logical puzzle. See the example file and test some values to see if this works how you require. The formulas could be put together as one and I've used the FLOOR function to round down to the nearest 10th of an hour.

Hope this helps.
DBY

Thanks DBY.... I will give this a try and see how it goes. Thanks again for your time and help! Much appreciated.