# Calculating work hours over multiple days

#### soundchaser99

##### New Member
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?

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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?

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

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

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.

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.

Replies
3
Views
649
Replies
1
Views
403
Replies
2
Views
531
Replies
0
Views
2K
Replies
4
Views
674

1,219,036
Messages
6,145,918
Members
450,655
Latest member
Calvest1

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

### Which adblocker are you using?

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

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