Removing non Work Hours from Excel calculation

L

lindaho98

I am trying to work out the minutes elapsed for a call monitoring
system. The hours monitored are between 05:30 and 19:00 - so if a call
gets logged outside of these hours then the minutes calculated will be
calculated from 05:30 the same day if logged on or after midnight or
05:30 the next day if logged before midnight (ie the next 05:30).

Any ideas ? - Thanks
 
B

Bob Phillips

Here is one way

=NETWORKDAYS(A1+1,B1-1)*13.5+MAX(MOD(A1,1),TIME(19,0,0))*24-MAX(MOD(A1,1),TI
ME(5,30,0))*24+MIN(MOD(B1,1),TIME(19,0,0))*24-MIN(MOD(B1,1),TIME(5,30,0))*24

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
L

lindaho98

Tried your method but it's returning 13.55, the actual minutes elapsed
should be 3 ?

Where A1=11/02/2006 02:12
B1=13/02/2006 05:33

So call raised at 02:12, but we ignore this and use the next start time
which is 5:30.

Any help would be appreciated ! Thanks
 
B

Bob Phillips

I didn't account for the start or end day being on a weekend (didn't seem
feasible to me). So try

=NETWORKDAYS(A1+1,B1-1)*13.5
+(MAX(MOD(A1,1),TIME(19,0,0))*24-MAX(MOD(A1,1),TIME(5,30,0))*24)*(WEEKDAY(A1
,2)<6)
+(MIN(MOD(B1,1),TIME(19,0,0))*24-MIN(MOD(B1,1),TIME(5,30,0))*24)*((WEEKDAY(B
1,2)<6))

This will return 0.05 in your example, as it is showing hours. If you want
it to show as time (00:03 or 12:22), then use

=NETWORKDAYS(A1+1,B1-1)*13.5/24
+(MAX(MOD(A1,1),TIME(19,0,0))-MAX(MOD(A1,1),TIME(5,30,0)))*(WEEKDAY(A1,2)<6)
+(MIN(MOD(B1,1),TIME(19,0,0))-MIN(MOD(B1,1),TIME(5,30,0)))*((WEEKDAY(B1,2)<6
))

and format as hh:mm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
L

lindaho98

That works fine for "out of hours" times - but I want the function to
be able to pick up "office hours" also.
so ... the above will not work if -

A1=13/02/2006 07:10
B1=13/02/2006 07:15
 
B

Bob Phillips

=(NETWORKDAYS(INT(A1),INT(B1))-2)*13.5/24
+(MAX(MOD(A1,1),TIME(19,0,0))-MAX(MOD(A1,1),TIME(5,30,0)))*(WEEKDAY(A1,2)<6)
+(MIN(MOD(B1,1),TIME(19,0,0))-MIN(MOD(B1,1),TIME(5,30,0)))*((WEEKDAY(B1,2)<6
))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
L

lindaho98

thanks, one last bit of assistance ... if the start and end date\time
falls on the same day then a negative value is returned ...

eg, A1 = 13/02/2006 07:55
B2 = 13/02/2006 08:00
 
B

Bob Phillips

Not for me it doesn't, I get 5 mins.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
L

lindaho98

Bob - here are the results I get where #### is a negative value.

A B C
10/02/2006 02:12 13/02/2006 06:33 14:33
09/02/2006 08:00 09/02/2006 08:33 0:32
11/02/2006 02:12 13/02/2006 06:33 ####
12/02/2006 02:12 13/02/2006 07:00 ####
 
L

lindaho98

Bob - here are the results I get where #### is a negative value.

A B C
10/02/2006 02:12 13/02/2006 06:33 14:33
09/02/2006 08:00 09/02/2006 08:33 0:32
11/02/2006 02:12 13/02/2006 06:33 ####
12/02/2006 02:12 13/02/2006 07:00 ####
 
B

Bob Phillips

I see the problem now.

=(NETWORKDAYS(INT(A3),INT(B3))-WEEKDAY(A3,2)<6-WEEKDAY(B3,2)<5)*13.5/24
+(MAX(MOD(A3,1),TIME(19,0,0))-MAX(MOD(A3,1),TIME(5,30,0)))*(WEEKDAY(A3,2)<6)
+(MIN(MOD(B3,1),TIME(19,0,0))-MIN(MOD(B3,1),TIME(5,30,0)))*((WEEKDAY(B3,2)<6
))

I think I have created quite a useful little formula with the help of your
(gentle) pushing <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

Let's get it right!

=(NETWORKDAYS(INT(A1),INT(B1))-WEEKDAY(A1,2)<6-WEEKDAY(B1,2)<6)*13.5/24
+(MAX(MOD(A1,1),TIME(19,0,0))-MAX(MOD(A1,1),TIME(5,30,0)))*(WEEKDAY(A1,2)<6)
+(MIN(MOD(B1,1),TIME(19,0,0))-MIN(MOD(B1,1),TIME(5,30,0)))*((WEEKDAY(B1,2)<6
))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
L

lindaho98

still incorrect !

A B C D
10/02/2006 02:12 13/02/2006 06:33 14:33 should be 1:03
09/02/2006 08:00 09/02/2006 08:33 14:03 should be 0:03
13/02/2006 06:30 13/02/2006 07:33 14:32 should be 1:03
12/02/2006 02:12 13/02/2006 07:00 1:30 Correct !
 
B

Bob Phillips

I get 1:03 for the third one, already, this formula corrects the second, and
I fail to see why the first is incorrect.

(NETWORKDAYS(INT(A1),INT(B1))-(WEEKDAY(A1,2)<6)-(WEEKDAY(B1,2)<6))*13.5/24
+(MAX(MOD(A1,1),TIME(19,0,0))-MAX(MOD(A1,1),TIME(5,30,0)))*(WEEKDAY(A1,2)<6)
+(MIN(MOD(B1,1),TIME(19,0,0))-MIN(MOD(B1,1),TIME(5,30,0)))*((WEEKDAY(B1,2)<6
))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
L

lindaho98

Thanks, but still incorrect.

10/02/2006 02:12 13/02/2006 06:33 #VALUE! should be 1:03
09/02/2006 08:00 09/02/2006 08:33 14:33 should be 0:03
13/02/2006 06:30 13/02/2006 07:33 0:33 should be 1:03
12/02/2006 02:12 13/02/2006 07:00 1:02 should be 1:30
 
L

lindaho98

sorry, one more thing - if I changed the start time to 06:30 (from
05:30), what else needs to be changed in the formula ?

thanks again
 
B

Bob Phillips

=(NETWORKDAYS(INT(A1),INT(B1))-(WEEKDAY(A1,2)<6)-(WEEKDAY(B1,2)<6))*13.5/24
+(MAX(MOD(A1,1),TIME(19,0,0))-MAX(MOD(A1,1),TIME(6,30,0)))*(WEEKDAY(A1,2)<6)
+(MIN(MOD(B1,1),TIME(19,0,0))-MIN(MOD(B1,1),TIME(6,30,0)))*(WEEKDAY(B1,2)<6)

It might be best to put the start and end times in cells, say E1 and F1 and
use that. makes it easier to change

=(NETWORKDAYS(INT(A1),INT(B1))-(WEEKDAY(A1,2)<6)-(WEEKDAY(B1,2)<6))*13.5/24
+(MAX(MOD(A1,1),F1)-MAX(MOD(A1,1),E1))*(WEEKDAY(A1,2)<6)
+(MIN(MOD(B1,1),F1)-MIN(MOD(B1,1),E1))*(WEEKDAY(B1,2)<6)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
L

lindaho98

the start dates and end dates based on the call so they are not static.
by changing the date from (5,30,0) to (6,30,0) does not output the
correct information ! still getting negative values (###)

A B C
13/02/2006 07:55 13/02/2006 07:59 ###
10/02/2006 18:55 13/02/2006 06:35 0:10
13/02/2006 22:10 14/02/2006 07:00 0:30
14/02/2006 07:55 14/02/2006 08:00 ###
13/02/2006 07:55 14/02/2006 08:00 12:35
 
Top