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

Some videos you may like

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

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
Joined
Dec 30, 2008
Messages
10,987
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jul 3, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jul 3, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 3, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 7, 2011
Messages
1,739
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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