exclude non-business hours and weekends

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!
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,739
Office Version
  1. 2010
Platform
  1. Windows
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.
Check row 23.

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.
Check row 20.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,987
Office Version
  1. 2019
Platform
  1. Windows
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.
 

KARANDREA

New Member
Joined
Jul 3, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
"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
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
309
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
For me, jasonb75 formula, work well. No errors and return corect value.
 

KARANDREA

New Member
Joined
Jul 3, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,739
Office Version
  1. 2010
Platform
  1. Windows
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))))
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,739
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,739
Office Version
  1. 2010
Platform
  1. Windows
hours.xlsx
ABCDEFGHI
11SUPPORT START TIMESUPORT END TIMEAssign Date ▼Owned Date ▼'Tykyjasonb75
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
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
309
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

KARANDREA

New Member
Joined
Jul 3, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,978
Messages
5,545,308
Members
410,675
Latest member
DLongmountain
Top