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.
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