Calculating work hours over multiple days

[soundchaser99]

This one has me stumped

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?

 

[barry houdini]

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]

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]

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]

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
	B	C	D	E	F	G	H
11	startdate	time1	time2	endDate	time3	time4	Totalworked
12	10/09/2006	8:00	13:45	13/09/2006	6:30	16:30	31.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]

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.