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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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

C
  • Question
Elapsed day, hours, minutes minus weekends
Replies
6
Views
379
Scott Huish
Scott Huish
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
630
felixstraube
F
E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
227
Fluff
Fluff
Asbestos_Jen
  • Question
Calculate end date and time excluding non-work hours
Replies
2
Views
376
Asbestos_Jen
Asbestos_Jen
K
  • Question
Production Time Calculator
Replies
4
Views
182
Drrellik
Drrellik

Forum statistics

Threads
1,214,413
Messages
6,119,374
Members
448,888
Latest member
Arle8907

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