ExcelBanter - Hours worked on job/per shift

Problem- Have a business that is open 24/7, we get jobs that can start
during any shift and continue from either 1 hr to a couple of days.
What I need to do is to figure out how much time is spent on each job
per shift. First shift is from 7:30 to 15:30, second from 15:30 to
23:30, and third from 23:30 to 7:30. I have three columns, first would
be "start date and time, second would be "Finish date and time" both
columns would be formated to "mm/dd/yy hh:mm:ss" with a third column
with total time. if a job starts at, say, 09/15/03 13:30:00, and ends
at 09/17/03 04:30:00, what formula would calculate the hours that each
shift worked on that job, not just total hours?

The closest thing I have found that remotely addresses my concern is
he http://www.cpearson.com/excel/datetimews.htm but unfortunately
everytime I try to use it, a job that started at 4:00am and ended at
noon (would be 3hrs 30min on third; 4hrs 30min on first) keeps comming
up as 8hrs on third. Also, the rounding in the formula prevents me
from getting minutes. I've also tried breaking it down into 4 shifts-
first (7:30 to 15:30), second (15:30 to 23:30) then a thirda (23:30 to
23:59) and a thirdb (00:00 to 7:30) And another thing, since we work 7
days a week, networkdays doesn't seem appropriate.

Any thoughts, suggestions or, if similar problem already addressed,
reference to that solution, highly appreciated.

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/