Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hours worked on job/per shift
For both of the replies, solution solved! I wish to thank you very much
for ending a two week headache! Sincerely ZiggyG ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Millitary Time to Hours worked and OT hours | Excel Worksheet Functions | |||
How do I convert date and time into shift worked? | Excel Discussion (Misc queries) | |||
Calculate Total hours worked during workdays within given shift time. | Excel Discussion (Misc queries) | |||
wages - multiply hours and minutes by number of hours worked | Excel Discussion (Misc queries) | |||
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) |