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?
 

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.
 

Forum statistics

Threads
1,141,429
Messages
5,706,405
Members
421,447
Latest member
arthuro2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top