# 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
@yky - I really can't understand what is going on. I am copying your formula as it is.
Not sure what's going on. I just copied your table and pasted it to Excel. I then copied C2 and pasted it by value. It appeared as 15/1/2020 13:44 (see J2), not a number. So, it is not a real date. It should never have worked for you. I don't know why you still got some correct results.

I did the same for my table and it worked, meaning dates are real dates, not text.

Another peculiar thing I found is the correct result. They have become huge numbers. This is the second time I notice it. Don't know what's going on.

hours.xlsx
ABCDEFGHIJ
1SUPPORT START TIMESUPORT END TIMEAssign DateOwned Datecorrect resultfirst formula resultjasonb75yky
20015/1/2020 13:4415/1/2020 14:001.11E+12#VALUE!#VALUE!#VALUE!15/1/2020 13:44
300########13/1/2020 09:165.28E+12#VALUE!#VALUE!#VALUE!
Sheet3

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### KARANDREA

##### New Member

You are right !! A & B are text. I changed them and now i get the correct results.

Of course I can't understand your formula - I am lost in it - but it seems to work!
I will check it for all inserts i have!
Thank you very much!

#### jasonb75

##### Well-known Member
You are right !! A & B are text. I changed them and now i get the correct results.
That may have also been the problem with my much shorter formula. The problem that @yky was seeing by copying your table was more likely caused by pasting dd/mm/yy format dates into a mm/dd/yy format computer.

As I mentioned in the post with the formula, it was based on an assumption, if my assumption was correct then the formula should work correctly.

#### KARANDREA

##### New Member
Thank you again! Both formula worked fine when I corrected the time as "time " and not as "text".
I tried both of them in my file.
I have some cases that are in lines 24 & 26 - 31 that i don't have the correct result.

XFProjects test.xlsx
BCDEGHI
1SUPPORT START TIMESUPORT END TIMEAssign DateOwned Datecorrect resultykyjasonb75
208:0018:0015/1/20 13:4415/1/20 14:000:160:160:16
308:0018:0011/1/20 18:5413/1/20 9:161:161:161:16
408:0018:0010/1/20 17:0310/1/20 17:050:020:020:02
508:0018:0016/1/20 10:3016/1/20 10:430:130:130:13
608:0018:0014/1/20 13:2814/1/20 13:300:020:020:02
708:0018:0017/1/20 13:4817/1/20 14:060:180:180:18
808:0018:0020/1/20 9:4920/1/20 9:580:090:090:09
909:0018:0027/1/20 13:2327/1/20 15:171:541:541:54
1008:0018:0031/1/20 15:2731/1/20 15:310:040:040:04
1108:0018:001/2/20 10:433/2/20 8:230:230:230:23
1208:0018:003/2/20 13:193/2/20 13:310:120:120:12
1308:0018:008/2/20 16:2610/2/20 8:280:280:280:28
1408:0018:006/3/20 18:469/3/20 10:152:152:152:15
1508:0018:008/5/20 9:068/5/20 9:360:300:300:30
1608:0018:0011/5/20 14:3811/5/20 15:260:480:480:48
1708:0018:0011/5/20 14:3311/5/20 15:270:540:540:54
1808:0018:002/3/20 19:112/3/20 20:220:000:000:00
1908:0018:002/3/20 19:092/3/20 20:240:000:000:00
2008:0018:0019/5/20 21:1120/5/20 10:552:552:552:55
2108:0018:0018/5/20 16:3318/5/20 16:420:090:090:09
2208:0018:0018/5/20 16:3218/5/20 16:420:100:100:10
2308:0018:0018/5/20 19:5718/5/20 21:140:000:000:00
2408:0018:0018/5/20 7:5518/5/20 18:1010:0010:0010:15
2508:0018:0018/5/20 9:5518/5/20 17:007:057:057:05
2608:0018:0020/2/2020 16:1021/2/2020 08:132:0316:030:13
2708:0018:002/3/2020 11:553/3/2020 08:386:4320:430:38
2808:0018:003/3/2020 17:314/3/2020 09:291:5815:581:29
2908:0018:003/3/2020 12:084/3/2020 09:307:2221:221:30
3008:0018:0010/3/2020 17:1211/3/2020 08:341:2215:220:34
3108:0018:0026/6/2020 17:4829/6/2020 09:201:3211:321:20
all

#### KARANDREA

##### New Member

I tried again one of my first formula and works! from the begining the problem was from the cell type...

=(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)

#### KARANDREA

##### New Member
Thank you very much for your time

XFProjects test.xlsx
BCDEGHIJ
1SUPPORT START TIMESUPORT END TIMEAssign DateOwned Datecorrect resultykyjasonb75new try
208:0018:0015/1/20 13:4415/1/20 14:000:160:160:160:16
308:0018:0011/1/20 18:5413/1/20 9:161:161:161:161:16
408:0018:0010/1/20 17:0310/1/20 17:050:020:020:020:02
508:0018:0016/1/20 10:3016/1/20 10:430:130:130:130:13
608:0018:0014/1/20 13:2814/1/20 13:300:020:020:020:02
708:0018:0017/1/20 13:4817/1/20 14:060:180:180:180:18
808:0018:0020/1/20 9:4920/1/20 9:580:090:090:090:09
909:0018:0027/1/20 13:2327/1/20 15:171:541:541:541:54
1008:0018:0031/1/20 15:2731/1/20 15:310:040:040:040:04
1108:0018:001/2/20 10:433/2/20 8:230:230:230:230:23
1208:0018:003/2/20 13:193/2/20 13:310:120:120:120:12
1308:0018:008/2/20 16:2610/2/20 8:280:280:280:280:28
1408:0018:006/3/20 18:469/3/20 10:152:152:152:152:15
1508:0018:008/5/20 9:068/5/20 9:360:300:300:300:30
1608:0018:0011/5/20 14:3811/5/20 15:260:480:480:480:48
1708:0018:0011/5/20 14:3311/5/20 15:270:540:540:540:54
1808:0018:002/3/20 19:112/3/20 20:220:000:000:000:00
1908:0018:002/3/20 19:092/3/20 20:240:000:000:000:00
2008:0018:0019/5/20 21:1120/5/20 10:552:552:552:552:55
2108:0018:0018/5/20 16:3318/5/20 16:420:090:090:090:09
2208:0018:0018/5/20 16:3218/5/20 16:420:100:100:100:10
2308:0018:0018/5/20 19:5718/5/20 21:140:000:000:000:00
2408:0018:0018/5/20 7:5518/5/20 18:1010:0010:0010:1510:00
2508:0018:0018/5/20 9:5518/5/20 17:007:057:057:057:05
2608:0018:0020/2/20 16:1021/2/20 8:132:0316:032:032:03
2708:0018:002/3/20 11:553/3/20 8:386:4320:436:436:43
2808:0018:003/3/20 17:314/3/20 9:291:5815:581:581:58
2908:0018:003/3/20 12:084/3/20 9:307:2221:227:227:22
3008:0018:0010/3/20 17:1211/3/20 8:341:2215:221:221:22
3108:0018:0026/6/20 17:4829/6/20 9:201:3211:321:321:32
all

#### yky

##### Well-known Member
Thank you again! Both formula worked fine when I corrected the time as "time " and not as "text".
I tried both of them in my file.
I have some cases that are in lines 24 & 26 - 31 that i don't have the correct result.
Again, some of the time are TEXT and some are real time. I don't know how you manage to work with them.

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