Removing non Work Hours from Excel calculation

  • Thread starter Thread starter lindaho98
  • Start date Start date
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
 
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)
 
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
 
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)
 
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
 
=(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)
 
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
 
Not for me it doesn't, I get 5 mins.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
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 ####
 
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 ####
 
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)
 
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)
 
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 !
 
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)
 
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
 
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
 
=(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)
 
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
 
Back
Top