exclude non-business hours and weekends

K

KARANDREA

New Member
Joined
Jul 3, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Thanks in advance for any help that can be offered.
I need to calculate the time between Assign date & Owned Date, but i must exclude weekends, if exists, and non business hours.
I am using ths formula at G column :
=(NETWORKDAYS(IF(WEEKDAY(D2;2)>5;WORKDAY(D2;1)+1/3;D2);E2)-1+MOD(E2;1)-MOD(IF(WEEKDAY(D2;2)>5;WORKDAY(D2;1)+1/3;D2);1))
It exclude weekends and start counting from Next business day from 8:00.

But i have different support hours, as example line 9, so i need to modify my formula with business hours in columns B & C.

I tried this formula in column H
=(NETWORKDAYS(D2;E2)-1)*(C2-B2)+IF(NETWORKDAYS(E2;E2);MEDIAN(MOD(E2;1);C2;B2);C2)-MEDIAN(NETWORKDAYS(D2;D2)*MOD(D2;1);C2;B2)
but i can't find out how change it in order start counting from Next business day. As example it gives me wrong result in cell H3.

1593784062323.png


I am confused.

Please help!
 
Sort by date Sort by votes
KARANDREA said:
This formula doesn't work.
If the range of support time is 8:00 - 18:00 and a ticket's "assigned time" is 19:45 and "owned time" is 21:00 the result should be 0 hours because it is out of support range.
Click to expand...
Check row 23.

KARANDREA said:
Now If the ticket's "assigned time" is 19:45 and "owned time" is 8:30 the next business day, the result must be 30 minutes.
It shouldn't count weekends and non business hours.
Click to expand...
Check row 20.
 
Upvote 0

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"
Try this one in G2 and fill down.

=E2-IF(INT(E2)>INT(D2),WORKDAY(D2,1)+B2,IF(MOD(D2,1)>C2,E2,D2))

This is based on the fact that in the example, the owned date and time is always less than 1 whole working day after the assign date.
 
Upvote 0
"G" column are the results that I must have.
"H" column are the results from my first formula
"I" column are the results from yky's formula
"J" column are the results from Jasonb75's formula.

With "yellow" are the correct results and with "red" are the incorrect.

@jasonb75 your formula seems to work better but in cases like when the ticket time "assigned" and "owned" is out of the certain range of columns B and C it should return "0:00" because this time shouldn't e counted. It is out of working hours.


XFProjects test.xlsx
BCDEGHIJ
1SUPPORT START TIMESUPORT END TIMEAssign DateOwned Datecorrect resultmy formula resultykyjasonb75
208:0018:0015/1/2020 13:4415/1/2020 14:000:160:166:000:16
308:0018:0011/1/2020 18:5413/1/2020 09:161:161:160:001:16
408:0018:0010/1/2020 17:0310/1/2020 17:050:020:029:050:02
508:0018:0016/1/2020 10:3016/1/2020 10:430:130:132:430:13
608:0018:0014/1/2020 13:2814/1/2020 13:300:020:025:300:02
708:0018:0017/1/2020 13:4817/1/2020 14:060:180:186:060:18
808:0018:0020/1/2020 09:4920/1/2020 09:580:090:090:000:09
909:0018:0027/1/2020 13:2327/1/2020 15:171:541:540:001:54
1008:0018:0031/1/2020 15:2731/1/2020 15:310:040:047:310:04
1108:0018:001/2/2020 10:433/2/2020 08:230:230:230:000:23
1208:0018:003/2/2020 13:193/2/2020 13:310:120:120:000:12
1308:0018:008/2/2020 16:2610/2/2020 08:280:280:280:000:28
1408:0018:006/3/2020 18:469/3/2020 10:152:1515:2910:002:15
1508:0018:008/5/2020 09:068/5/2020 09:360:300:301:360:30
1608:0018:0011/5/2020 14:3811/5/2020 15:260:480:480:000:48
1708:0018:0011/5/2020 14:3311/5/2020 15:270:540:540:000:54
1808:0018:002/3/2020 19:112/3/2020 20:220:001:110:001:11
1908:0018:002/3/2020 19:092/3/2020 20:240:001:150:001:15
2008:0018:0019/5/2020 21:1120/5/2020 10:552:5513:4412:552:55
2108:0018:0018/5/2020 16:3318/5/2020 16:420:090:090:000:09
2208:0018:0018/5/2020 16:3218/5/2020 16:420:100:100:000:10
2308:0018:0018/5/2020 19:5718/5/2020 21:140:001:170:001:17
all
 
Upvote 0
For me, jasonb75 formula, work well. No errors and return corect value.
 
Upvote 0
The formula is working well.
but for example in line 18, 19 & 23 the "assigned & owned" hours is out of working hours range (8:00 to 18:00)
I don't want to count those hours, they should be excluded.
 
Upvote 0
Did you get my formula right? How come your result is different from mine?

hours.xlsx
ABCDEFGH
11SUPPORT START TIMESUPORT END TIMEAssign Date ▼Owned Date ▼'T▼
228:00:00 AM6:00:00 PM1/15/2020 13:441/15/2020 14:000:160:160:16
338:00:00 AM6:00:00 PM1/11/2020 18:541/13/2020 9:161:161:161:16
448:00:00 AM6:00:00 PM1/10/2020 17:031/10/2020 17:050:020:020:02
558:00:00 AM6:00:00 PM1/16/2020 10:301/16/2020 10:430:130:130:13
668:00:00 AM6:00:00 PM1/14/2020 13:281/14/2020 13:300:020:020:02
778:00:00 AM6:00:00 PM1/17/2020 13:481/17/2020 14:060:180:180:18
888:00:00 AM6:00:00 PM1/20/2020 9:491/20/2020 9:580:090:090:09
999:00:00 AM6:00:00 PM1/27/2020 13:231/27/2020 15:171:541:541:54
10108:00:00 AM6:00:00 PM1/31/2020 15:271/31/2020 15:310:040:040:04
11118:00:00 AM6:00:00 PM2/1/2020 10:432/3/2020 8:230:230:230:23
12128:00:00 AM6:00:00 PM2/3/2020 13:192/3/2020 13:310:120:120:12
13138:00:00 AM6:00:00 PM2/8/2020 16:262/10/2020 8:280:280:280:28
14148:00:00 AM6:00:00 PM3/6/2020 18:463/9/2020 10:1515:292:152:15
15158:00:00 AM6:00:00 PM5/8/2020 9:065/8/2020 9:360:300:300:30
16168:00:00 AM6:00:00 PM5/11/2020 14:385/11/2020 15:260:480:480:48
17178:00:00 AM6:00:00 PM5/11/2020 14:335/11/2020 15:270:540:540:54
18188:00:00 AM6:00:00 PM3/2/2020 19:113/2/2020 20:221:110:000:00
19198:00:00 AM6:00:00 PM3/2/2020 19:093/2/2020 20:241:150:000:00
20208:00:00 AM6:00:00 PM5/19/2020 21:115/20/2020 10:5513:442:552:55
21218:00:00 AM6:00:00 PM5/18/2020 16:335/18/2020 16:420:090:090:09
22228:00:00 AM6:00:00 PM5/18/2020 16:325/18/2020 16:420:100:100:10
23238:00:00 AM6:00:00 PM5/18/2020 19:575/18/2020 21:141:170:000:00
24248:00:00 AM6:00:00 PM5/24/2020 7:525/24/2020 19:520:000:00
25258:00:00 AM6:00:00 PM5/22/2020 7:525/23/2020 10:2210:0010:00
26268:00:00 AM6:00:00 PM5/22/2020 7:525/25/2020 6:2310:0010:00
27279:00:00 AM6:00:00 PM5/21/2020 7:525/23/2020 19:2118:0018:00
Sheet2
Cell Formulas
RangeFormula
H2:H27H2=IF(IF(WEEKDAY(E2,2)>5,WORKDAY(E2,1)+C2-3,IF(AND(WEEKDAY(E2,2)=1,MOD(E2,1)<=B2),WORKDAY(E2,1)+C2-4,IF(MOD(E2,1)<MOD($C2,1),E2,WORKDAY(INT(E2),0)+C2)))<IF(OR(WEEKDAY(D2,2)>5,AND(WEEKDAY(D2,2)=5,MOD(D2,1)>C2)),WORKDAY(D2,1)+B2,IF(MOD(D2,1)>B2,IF(MOD(D2,1)>C2,WORKDAY(D2,1)+B2,D2),INT(D2)+B2)),0,IF(IF(WEEKDAY(E2,2)>5,WORKDAY(E2,1)+C2-3,IF(AND(WEEKDAY(E2,2)=1,MOD(E2,1)<=B2),WORKDAY(E2,1)+C2-4,IF(MOD(E2,1)<MOD($C2,1),E2,WORKDAY(INT(E2),0)+C2)))-IF(OR(WEEKDAY(D2,2)>5,AND(WEEKDAY(D2,2)=5,MOD(D2,1)>C2)),WORKDAY(D2,1)+B2,IF(MOD(D2,1)>B2,IF(MOD(D2,1)>C2,WORKDAY(D2,1)+B2,D2),INT(D2)+B2))>1,IF(WEEKDAY(E2,2)>5,WORKDAY(E2,1)+C2-3,IF(AND(WEEKDAY(E2,2)=1,MOD(E2,1)<=B2),WORKDAY(E2,1)+C2-4,IF(MOD(E2,1)<MOD($C2,1),E2,WORKDAY(INT(E2),0)+C2)))-IF(OR(WEEKDAY(D2,2)>5,AND(WEEKDAY(D2,2)=5,MOD(D2,1)>C2)),WORKDAY(D2,1)+B2,IF(MOD(D2,1)>B2,IF(MOD(D2,1)>C2,WORKDAY(D2,1)+B2,D2),INT(D2)+B2))-INT(IF(WEEKDAY(E2,2)>5,WORKDAY(E2,1)+C2-3,IF(AND(WEEKDAY(E2,2)=1,MOD(E2,1)<=B2),WORKDAY(E2,1)+C2-4,IF(MOD(E2,1)<MOD($C2,1),E2,WORKDAY(INT(E2),0)+C2)))-IF(OR(WEEKDAY(D2,2)>5,AND(WEEKDAY(D2,2)=5,MOD(D2,1)>C2)),WORKDAY(D2,1)+B2,IF(MOD(D2,1)>B2,IF(MOD(D2,1)>C2,WORKDAY(D2,1)+B2,D2),INT(D2)+B2)))*(1-C2+B2),IF(WEEKDAY(E2,2)>5,WORKDAY(E2,1)+C2-3,IF(AND(WEEKDAY(E2,2)=1,MOD(E2,1)<=B2),WORKDAY(E2,1)+C2-4,IF(MOD(E2,1)<MOD($C2,1),E2,WORKDAY(INT(E2),0)+C2)))-IF(OR(WEEKDAY(D2,2)>5,AND(WEEKDAY(D2,2)=5,MOD(D2,1)>C2)),WORKDAY(D2,1)+B2,IF(MOD(D2,1)>B2,IF(MOD(D2,1)>C2,WORKDAY(D2,1)+B2,D2),INT(D2)+B2))))
 
Upvote 0
KARANDREA said:
The formula is working well.
but for example in line 18, 19 & 23 the "assigned & owned" hours is out of working hours range (8:00 to 18:00)
I don't want to count those hours, they should be excluded.
Click to expand...
jasonb75's formula works well for rows 1-23 (I don't know how you got jasonb75's results in your table. They are different from what I got using his formula). It is the rows after 23, which is not in your table, that his formula doesn't work.
 
Upvote 0
hours.xlsx
ABCDEFGHI
11SUPPORT START TIMESUPORT END TIMEAssign Date ▼Owned Date ▼'T▼ykyjasonb75
228:00:00 AM6:00:00 PM1/15/2020 13:441/15/2020 14:000:160:160:160:16
338:00:00 AM6:00:00 PM1/11/2020 18:541/13/2020 9:161:161:161:161:16
448:00:00 AM6:00:00 PM1/10/2020 17:031/10/2020 17:050:020:020:020:02
558:00:00 AM6:00:00 PM1/16/2020 10:301/16/2020 10:430:130:130:130:13
668:00:00 AM6:00:00 PM1/14/2020 13:281/14/2020 13:300:020:020:020:02
778:00:00 AM6:00:00 PM1/17/2020 13:481/17/2020 14:060:180:180:180:18
888:00:00 AM6:00:00 PM1/20/2020 9:491/20/2020 9:580:090:090:090:09
999:00:00 AM6:00:00 PM1/27/2020 13:231/27/2020 15:171:541:541:541:54
10108:00:00 AM6:00:00 PM1/31/2020 15:271/31/2020 15:310:040:040:040:04
11118:00:00 AM6:00:00 PM2/1/2020 10:432/3/2020 8:230:230:230:230:23
12128:00:00 AM6:00:00 PM2/3/2020 13:192/3/2020 13:310:120:120:120:12
13138:00:00 AM6:00:00 PM2/8/2020 16:262/10/2020 8:280:280:280:280:28
14148:00:00 AM6:00:00 PM3/6/2020 18:463/9/2020 10:1515:292:152:152:15
15158:00:00 AM6:00:00 PM5/8/2020 9:065/8/2020 9:360:300:300:300:30
16168:00:00 AM6:00:00 PM5/11/2020 14:385/11/2020 15:260:480:480:480:48
17178:00:00 AM6:00:00 PM5/11/2020 14:335/11/2020 15:270:540:540:540:54
18188:00:00 AM6:00:00 PM3/2/2020 19:113/2/2020 20:221:110:000:000:00
19198:00:00 AM6:00:00 PM3/2/2020 19:093/2/2020 20:241:150:000:000:00
20208:00:00 AM6:00:00 PM5/19/2020 21:115/20/2020 10:5513:442:552:552:55
21218:00:00 AM6:00:00 PM5/18/2020 16:335/18/2020 16:420:090:090:090:09
22228:00:00 AM6:00:00 PM5/18/2020 16:325/18/2020 16:420:100:100:100:10
23238:00:00 AM6:00:00 PM5/18/2020 19:575/18/2020 21:141:170:000:000:00
24248:00:00 AM6:00:00 PM5/24/2020 7:525/24/2020 19:520:000:0012:00
25258:00:00 AM6:00:00 PM5/22/2020 7:525/23/2020 10:2210:0010:00#############
26268:00:00 AM6:00:00 PM5/22/2020 7:525/25/2020 6:2310:0010:00#############
27279:00:00 AM6:00:00 PM5/21/2020 7:525/23/2020 19:2118:0018:0010:21
289:00:00 AM6:00:00 PM5/21/2020 8:525/23/2020 19:2119:0018:0010:21
299:00:00 AM6:00:00 PM5/21/2020 9:525/23/2020 19:2120:0017:0810:21
Sheet2
 
Upvote 0
I guess one of the columns B, C, D or E are not numbers.
Column C has the data aligned to the left. Check that data.
Jasonb75's formula follows the imposed rules. Every result is correct.
 
Upvote 0
@yky - I really can't understand what is going on. I am copying your formula as it is.

=IF(IF(WEEKDAY(E2;2)>5;WORKDAY(E2;1)+C2-3;IF(AND(WEEKDAY(E2;2)=1;MOD(E2;1)<=B2);WORKDAY(E2;1)+C2-4;IF(MOD(E2;1)<MOD($C2;1);E2;WORKDAY(INT(E2);0)+C2)))<IF(OR(WEEKDAY(D2;2)>5;AND(WEEKDAY(D2;2)=5;MOD(D2;1)>C2));WORKDAY(D2;1)+B2;IF(MOD(D2;1)>B2;IF(MOD(D2;1)>C2;WORKDAY(D2;1)+B2;D2);INT(D2)+B2));0;IF(IF(WEEKDAY(E2;2)>5;WORKDAY(E2;1)+C2-3;IF(AND(WEEKDAY(E2;2)=1;MOD(E2;1)<=B2);WORKDAY(E2;1)+C2-4;IF(MOD(E2;1)<MOD($C2;1);E2;WORKDAY(INT(E2);0)+C2)))-IF(OR(WEEKDAY(D2;2)>5;AND(WEEKDAY(D2;2)=5;MOD(D2;1)>C2));WORKDAY(D2;1)+B2;IF(MOD(D2;1)>B2;IF(MOD(D2;1)>C2;WORKDAY(D2;1)+B2;D2);INT(D2)+B2))>1;IF(WEEKDAY(E2;2)>5;WORKDAY(E2;1)+C2-3;IF(AND(WEEKDAY(E2;2)=1;MOD(E2;1)<=B2);WORKDAY(E2;1)+C2-4;IF(MOD(E2;1)<MOD($C2;1);E2;WORKDAY(INT(E2);0)+C2)))-IF(OR(WEEKDAY(D2;2)>5;AND(WEEKDAY(D2;2)=5;MOD(D2;1)>C2));WORKDAY(D2;1)+B2;IF(MOD(D2;1)>B2;IF(MOD(D2;1)>C2;WORKDAY(D2;1)+B2;D2);INT(D2)+B2))-INT(IF(WEEKDAY(E2;2)>5;WORKDAY(E2;1)+C2-3;IF(AND(WEEKDAY(E2;2)=1;MOD(E2;1)<=B2);WORKDAY(E2;1)+C2-4;IF(MOD(E2;1)<MOD($C2;1);E2;WORKDAY(INT(E2);0)+C2)))-IF(OR(WEEKDAY(D2;2)>5;AND(WEEKDAY(D2;2)=5;MOD(D2;1)>C2));WORKDAY(D2;1)+B2;IF(MOD(D2;1)>B2;IF(MOD(D2;1)>C2;WORKDAY(D2;1)+B2;D2);INT(D2)+B2)))*(1-C2+B2);IF(WEEKDAY(E2;2)>5;WORKDAY(E2;1)+C2-3;IF(AND(WEEKDAY(E2;2)=1;MOD(E2;1)<=B2);WORKDAY(E2;1)+C2-4;IF(MOD(E2;1)<MOD($C2;1);E2;WORKDAY(INT(E2);0)+C2)))-IF(OR(WEEKDAY(D2;2)>5;AND(WEEKDAY(D2;2)=5;MOD(D2;1)>C2));WORKDAY(D2;1)+B2;IF(MOD(D2;1)>B2;IF(MOD(D2;1)>C2;WORKDAY(D2;1)+B2;D2);INT(D2)+B2))))



---------------------------
XFProjects test.xlsx
BCDEGHIJ
1SUPPORT START TIMESUPORT END TIMEAssign DateOwned Datecorrect resultfirst formula resultjasonb75yky
208:0018:0015/1/2020 13:4415/1/2020 14:000:160:160:166:00
308:0018:0011/1/2020 18:5413/1/2020 09:161:161:161:160:00
408:0018:0010/1/2020 17:0310/1/2020 17:050:020:020:029:05
508:0018:0016/1/2020 10:3016/1/2020 10:430:130:130:132:43
608:0018:0014/1/2020 13:2814/1/2020 13:300:020:020:025:30
708:0018:0017/1/2020 13:4817/1/2020 14:060:180:180:186:06
808:0018:0020/1/2020 09:4920/1/2020 09:580:090:090:090:00
909:0018:0027/1/2020 13:2327/1/2020 15:171:541:541:540:00
1008:0018:0031/1/2020 15:2731/1/2020 15:310:040:040:047:31
1108:0018:001/2/2020 10:433/2/2020 08:230:230:230:230:00
1208:0018:003/2/2020 13:193/2/2020 13:310:120:120:120:00
1308:0018:008/2/2020 16:2610/2/2020 08:280:280:280:280:00
1408:0018:006/3/2020 18:469/3/2020 10:152:1515:292:1510:00
1508:0018:008/5/2020 09:068/5/2020 09:360:300:300:301:36
1608:0018:0011/5/2020 14:3811/5/2020 15:260:480:480:480:00
1708:0018:0011/5/2020 14:3311/5/2020 15:270:540:540:540:00
1808:0018:002/3/2020 19:112/3/2020 20:220:001:111:110:00
1908:0018:002/3/2020 19:092/3/2020 20:240:001:151:150:00
2008:0018:0019/5/2020 21:1120/5/2020 10:552:5513:442:5512:55
2108:0018:0018/5/2020 16:3318/5/2020 16:420:090:090:090:00
2208:0018:0018/5/2020 16:3218/5/2020 16:420:100:100:100:00
2308:0018:0018/5/2020 19:5718/5/2020 21:140:001:171:170:00
2408:0018:0018/5/2020 07:5518/5/2020 18:1010:0010:1510:150:00
2509:0017:0018/5/2020 09:5518/5/2020 17:007:057:057:050:00
all
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
398
felixstraube
F
M
  • Question Question
Formula to Split Start/End Times into Hour Brackets
Replies
4
Views
236
felixstraube
F
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
571
Asbestos_Jen
Asbestos_Jen
C
  • Question Question
Elapsed day, hours, minutes minus weekends
Replies
6
Views
733
Scott Huish
Scott Huish
S
  • Question Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
431
awoohaw
awoohaw

Forum statistics

Threads
1,223,268
Messages
6,171,100
Members
452,379
Latest member
IainTru

Share this page

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