Working hours between two points in time

Working hours between two points in time

Didn't find your answer?

I have a spreadsheet, which records the start/finish points of individual jobs, which may last up to several months.

I would like to calculate the duration of the jobs in working hours, based on say, a standard working day of 09:00 - 17:00.

Suppose I have the following entries:

Cell A2 10/10/2003 (Start Date)
Cell B2 16:00 (Start Time)
Cell C2 15/03/2004 (End Date)
Cell D2 10:00 (End Time)
Cell E2 ? (Duration)

Can anyone suggest a clever formula/function which will calculate the duration in cell E2?

Tony Stevens

Tony Stevens

Replies (2)

Please login or register to join the discussion.

Routemaster image
By tom123
29th Mar 2004 11:56

Try this
enter 17:00 in E1 (end of working day in hours and minutes)
enter 09:00 in F1 (beginning of day in hours and minutes)

E2=E1-B2 (hours worked on first day)
F2=D2-F1 (hours worked on last day)
G2=E2+F2 (total hours worked on part days)
H2-C2-A2-1 (complete whole days worked)
I2=H2+G2 (format answer, custom, dd hh:mm)

Not sure about weekends though!

Presume you meant start 10 march 04 not 03

my answer four days 2 hours

Thanks (0)
Tony Stevens
By tony.stevens
29th Mar 2004 13:11

Many thanks
I also managed to find an answer at:

http://www.cpearson.com/excel/DateTimeWS.htm

This uses the NETWORKDAYS function to eliminate weekends as well.

I would recommend this site be checked out, especially for data/time problems.

Once again many thanks

Thanks (0)