Calculating work hours over multiple days

soundchaser99

New Member
Joined
Mar 30, 2006
Messages
16
This one has me stumped :confused:

I need to track the total time taken to complete a job in hours. The problem is the job may take several days and I only want to count actual work hours.

I've tried using DATEDIF function and looked through some of the archives but so far no luck :(

BAsically, a user enters the following:

Start date (A1), Start Time (B1), End Date (A3), End Time (B3).

The work day is 9hr long - from 6.30am to 4.30pm. (1hr lunch - not to be included)

So, for example if they started a job at 8am on the 10th, and finished at 2pm on the 12th the total work time should equal 23 hours, broken down as follows:

10th 8am-4.30pm = 8.5hr - lunch = 7.5hr
11th 6.30am - 4.30pm = 10hr - lunch = 9hr
12th 6.30am - 2pm = 7.5hr - lunch = 6.5hr

7.5+9.0+6.5 = 23hrs

Any ideas?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You need to specify the exact time of the lunch break, I think.

Will the start and end times/dates always be within your working hours?
 

soundchaser99

New Member
Joined
Mar 30, 2006
Messages
16
There are actually 2 half-hour breaks, one : 9.30-10am the other 12.30-1pm.

And yes, the start and finish times will always be within the normal working hours
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try

=(NETWORKDAYS(A1,A3)-1)*3/8+B3-B1+MEDIAN(13/24-B3,0,1/48)+MEDIAN(5/12-B3,0,1/48)-MEDIAN(13/24-B1,0,1/48)-MEDIAN(5/12-B1,0,1/48)

format as [h]:mm
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello to you all,

When I started working for a solution no one has posted an answer by the time I get around to do it, there was already an answer provide by Barry.

However because I worked on an answer here is another variance of a solution:
Book1
BCDEFGH
11startdatetime1time2endDatetime3time4Totalworked
1210/09/20068:0013:4513/09/20066:3016:3031.75
Sheet1


The formula used is :

=((NETWORKDAYS(startdate,endDate)>1)*(NETWORKDAYS(startdate,endDate)-1)*9)+((time2-time1-1/24)+(time4-time3-1/24))*24


If you try first double check that the result returned is exact. I think it is but you never know.
 

soundchaser99

New Member
Joined
Mar 30, 2006
Messages
16
Thanks for the help, the first solution works perfectly.

Barry, if it's not too much trouble, could you explain how each part in the formula works?

I think I can adapt that formula in other areas but it may require different parameters such as more or less work hours during the day, overtime etc. so if I knew how it worked then I should be able adjust it myself.

thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,911
Messages
5,541,543
Members
410,549
Latest member
Anaarchie
Top