Calculate only working hours between two dates with several constraints (includes weekend, excludes holidays, working hours ranges different each day

Status
Not open for further replies.

jilianbenss

New Member
Joined
Aug 20, 2018
Messages
3
Hi all,



I have read many threads regarding the calculation of working hours between two dates, including the following thread
https://www.mrexcel.com/forum/excel-questions/426101-calculate-only-working-hours-between-two-dates-excluding-weekends.html and the great answers from member barry houdini and others (e.g. pedrowave). I am really grateful for the patience and quality of barry's and other members of this board.

I am facing a difficult calculation as there are many constraints.

I need to calculate the time elapsed between two tickets raised. A client raises a ticket, at any time and any day, and our staff resolves the ticket within our working hours.
I then need to calculate the time it takes to resolve the ticket within the working hours, including the weekend, and excluding bank holidays. If a ticket is resolved outside of working hours and days, only the time WITHIN the working hours and days should be counted.

I have found great answers that either include or exclude weekend. However, I have 3 major constraints that make all the formulas and solutions proposed in the mentioned thread unusable:




  • I manage several locations, each location has different working hours and different working days. E.g. NYC is open from Monday to Sunday, with different hours for each day. Los Angeles only Monday to Friday. New Orleans Monday to Saturday. Again, the working hours are different each day (e.g. Monday to Friday 5am 730pm, Saturday 6am - 2pm, Sunday 9am - 5pm).
  • Each location has different Bank Holidays because they're located in different states (and sometimes countries).
  • Some of our locations have working hours that finish late into the night (e.g. 5am to 1am).


    I know NETWORKDAYS cannot be used since it's a Mon-Fri formula, and I've tried to adapt barry houdini's formula #16 :


=(SUMPRODUCT(INT((WEEKDAY(A2-{2,3,4,5,6,7})+INT(B2)-INT(A2))/7),{9,9,9,9,9,4})-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10<b2),lookup(weekday(h$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16)) span="" 24<=""></b2),lookup(weekday(h$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16))>

<b2),lookup(weekday(h$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16)) 24

A2 B2 being start date and finish date, H1:H10 being holidays.

However, this formula has fixed working hours, where I need to change the working hours for each day and each location. Also, I cannot seem to calculate properly working hours when they extend to the day after (e.g. office open from5am Tuesday to 1am Wednesday).



The file looks like this, I'm trying to calculate in the column Working Hours
Tab 1:</b2),lookup(weekday(h$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16))>


[TABLE="class: grid, width: 617"]
<tbody>[TR]
[TD="width: 100, bgcolor: transparent"] Ticket number
[/TD]
[TD="width: 139, bgcolor: transparent"]Organization
[/TD]
[TD="width: 175, bgcolor: transparent"]Date Reported
[/TD]
[TD="width: 141, bgcolor: transparent"]Date of resolution
[/TD]
[TD="width: 269, bgcolor: transparent"]Working Hours
[/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]
12583
[/TD]
[TD="width: 139, bgcolor: transparent"]Philadelphia
[/TD]
[TD="width: 175, bgcolor: transparent"]
11/02/2017 16:59
[/TD]
[TD="width: 141, bgcolor: transparent"]
13/02/2017 10:29
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]
19029
[/TD]
[TD="width: 139, bgcolor: transparent"]NYC
[/TD]
[TD="width: 175, bgcolor: transparent"]
8/11/2016 17:51
[/TD]
[TD="width: 141, bgcolor: transparent"]
8/11/2016 20:31
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]
12584
[/TD]
[TD="width: 139, bgcolor: transparent"]LA
[/TD]
[TD="width: 175, bgcolor: transparent"]
11/02/2017 16:59
[/TD]
[TD="width: 141, bgcolor: transparent"]
13/02/2017 10:29
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]
12488
[/TD]
[TD="width: 139, bgcolor: transparent"]Kuala Lumpur
[/TD]
[TD="width: 175, bgcolor: transparent"]
9/02/2017 19:59
[/TD]
[TD="width: 141, bgcolor: transparent"]
10/02/2017 14:15
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]
13284
[/TD]
[TD="width: 139, bgcolor: transparent"]Mumbai
[/TD]
[TD="width: 175, bgcolor: transparent"]
24/02/2017 21:50
[/TD]
[TD="width: 141, bgcolor: transparent"]
25/02/2017 9:27
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]
19460
[/TD]
[TD="width: 139, bgcolor: transparent"]New Orleans
[/TD]
[TD="width: 175, bgcolor: transparent"]
18/11/2016 11:54
[/TD]
[TD="width: 141, bgcolor: transparent"]
19/11/2016 10:54
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]
14784
[/TD]
[TD="width: 139, bgcolor: transparent"]Charlotteville
[/TD]
[TD="width: 175, bgcolor: transparent"]
27/03/2017 0:00
[/TD]
[TD="width: 141, bgcolor: transparent"]
27/03/2017 13:18
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Tab 2 Working hours:
[TABLE="class: grid, width: 1080"]
<tbody>[TR]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 97, bgcolor: transparent"]Monday Opening
[/TD]
[TD="width: 91, bgcolor: transparent"]Monday Closing
[/TD]
[TD="width: 98, bgcolor: transparent"]Tuesday Opening
[/TD]
[TD="width: 92, bgcolor: transparent"]Tuesday Closing
[/TD]
[TD="width: 121, bgcolor: transparent"]Wednesday Opening
[/TD]
[TD="width: 116, bgcolor: transparent"]Wednesday Closing
[/TD]
[TD="width: 104, bgcolor: transparent"]Thursday Opening
[/TD]
[TD="width: 98, bgcolor: transparent"]Thursday Closing
[/TD]
[TD="width: 89, bgcolor: transparent"]Friday Opening
[/TD]
[TD="width: 81, bgcolor: transparent"]Friday Closing
[/TD]
[TD="width: 102, bgcolor: transparent"]Saturday Opening
[/TD]
[TD="width: 97, bgcolor: transparent"]Saturday Closing
[/TD]
[TD="width: 93, bgcolor: transparent"]Sunday Opening
[/TD]
[TD="width: 65, bgcolor: transparent"]Sunday Closing
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Business
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NYC
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
1:00 AM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
1:00 AM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
1:00 AM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
1:00 AM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
1:00 AM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
12:00 AM
[/TD]
[TD="bgcolor: transparent"]
2:00 PM
[/TD]
[TD="bgcolor: transparent"]
12:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Philadelphia
[/TD]
[TD="bgcolor: transparent"]
5:30 AM
[/TD]
[TD="bgcolor: transparent"]
7:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:30 AM
[/TD]
[TD="bgcolor: transparent"]
7:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:30 AM
[/TD]
[TD="bgcolor: transparent"]
7:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:30 AM
[/TD]
[TD="bgcolor: transparent"]
7:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:30 AM
[/TD]
[TD="bgcolor: transparent"]
7:00 PM
[/TD]
[TD="bgcolor: transparent"]
6:00 AM
[/TD]
[TD="bgcolor: transparent"]
2:00 PM
[/TD]
[TD="bgcolor: transparent"]
9:00 AM
[/TD]
[TD="bgcolor: transparent"]
5:00 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Charlotteville
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
8:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
8:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
8:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
8:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
8:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
1:00 PM
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New Orleans
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
4:00 AM
[/TD]
[TD="bgcolor: transparent"]
1:00 PM
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LA
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kuala Lumpur
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]
5:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 PM
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mumbai
[/TD]
[TD="bgcolor: transparent"]
6:00 AM
[/TD]
[TD="bgcolor: transparent"]
9:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 AM
[/TD]
[TD="bgcolor: transparent"]
9:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 AM
[/TD]
[TD="bgcolor: transparent"]
9:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 AM
[/TD]
[TD="bgcolor: transparent"]
9:00 AM
[/TD]
[TD="bgcolor: transparent"]
6:00 AM
[/TD]
[TD="bgcolor: transparent"]
9:00 AM
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[/TR]
</tbody>[/TABLE]


Tab 3 Bank holidays to exclude:

[TABLE="class: grid, width: 436"]
<tbody>[TR]
[TD="width: 82, bgcolor: transparent"]Philadelphia
[/TD]
[TD="width: 82, bgcolor: transparent"]NYC
[/TD]
[TD="width: 91, bgcolor: transparent"]Charlotteville
[/TD]
[TD="width: 82, bgcolor: transparent"]New Orleans
[/TD]
[TD="width: 82, bgcolor: transparent"]Kuala Lumpur
[/TD]
[TD="width: 82, bgcolor: transparent"]Mumbai
[/TD]
[TD="width: 82, bgcolor: transparent"]LA
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
1/01/2017
[/TD]
[TD="bgcolor: transparent"]
1/01/2016
[/TD]
[TD="bgcolor: transparent"]
1/01/2017
[/TD]
[TD="bgcolor: transparent"]
1/01/2016
[/TD]
[TD="bgcolor: transparent"]
1/01/2017
[/TD]
[TD="bgcolor: transparent"]
1/01/2016
[/TD]
[TD="bgcolor: transparent"]
1/01/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
2/02/2017
[/TD]
[TD="bgcolor: transparent"]
26/01/2016
[/TD]
[TD="bgcolor: transparent"]
2/02/2017
[/TD]
[TD="bgcolor: transparent"]
26/01/2016
[/TD]
[TD="bgcolor: transparent"]
2/02/2017
[/TD]
[TD="bgcolor: transparent"]
26/01/2016
[/TD]
[TD="bgcolor: transparent"]
2/02/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
26/01/2017
[/TD]
[TD="bgcolor: transparent"]
7/03/2016
[/TD]
[TD="bgcolor: transparent"]
26/01/2017
[/TD]
[TD="bgcolor: transparent"]
7/03/2016
[/TD]
[TD="bgcolor: transparent"]
26/01/2017
[/TD]
[TD="bgcolor: transparent"]
7/03/2016
[/TD]
[TD="bgcolor: transparent"]
26/01/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
14/04/2017
[/TD]
[TD="bgcolor: transparent"]
25/03/2016
[/TD]
[TD="bgcolor: transparent"]
14/04/2017
[/TD]
[TD="bgcolor: transparent"]
25/03/2016
[/TD]
[TD="bgcolor: transparent"]
14/04/2017
[/TD]
[TD="bgcolor: transparent"]
25/03/2016
[/TD]
[TD="bgcolor: transparent"]
14/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
15/04/2017
[/TD]
[TD="bgcolor: transparent"]
28/03/2016
[/TD]
[TD="bgcolor: transparent"]
15/04/2017
[/TD]
[TD="bgcolor: transparent"]
28/03/2016
[/TD]
[TD="bgcolor: transparent"]
15/04/2017
[/TD]
[TD="bgcolor: transparent"]
28/03/2016
[/TD]
[TD="bgcolor: transparent"]
15/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
16/04/2017
[/TD]
[TD="bgcolor: transparent"]
25/04/2016
[/TD]
[TD="bgcolor: transparent"]
16/04/2017
[/TD]
[TD="bgcolor: transparent"]
25/04/2016
[/TD]
[TD="bgcolor: transparent"]
16/04/2017
[/TD]
[TD="bgcolor: transparent"]
25/04/2016
[/TD]
[TD="bgcolor: transparent"]
16/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
17/04/2017
[/TD]
[TD="bgcolor: transparent"]
6/06/2016
[/TD]
[TD="bgcolor: transparent"]
17/04/2017
[/TD]
[TD="bgcolor: transparent"]
6/06/2016
[/TD]
[TD="bgcolor: transparent"]
17/04/2017
[/TD]
[TD="bgcolor: transparent"]
6/06/2016
[/TD]
[TD="bgcolor: transparent"]
17/04/2017
[/TD]
[/TR]
</tbody>[/TABLE]


If anyone could help me I've been literally spending days on this.


Thanks

Cheers
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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
Back
Top