exclude non-business hours and weekends

KARANDREA

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

I am confused.

yky

Well-known Member
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.

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
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
"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
For me, jasonb75 formula, work well. No errors and return corect value.

KARANDREA

New Member

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

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

Replies
7
Views
49
Replies
29
Views
508
Replies
9
Views
207
Replies
19
Views
209
Replies
5
Views
60