Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

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)

wrote in message
ups.com...
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

Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

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)

wrote in message
oups.com...
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

Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

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

wrote in message
oups.com...
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

Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
ups.com...
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

Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

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)

wrote in message
oups.com...
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 ####

Removing non Work Hours from Excel calculation
 

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)

wrote in message
oups.com...
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 ####

Removing non Work Hours from Excel calculation
 

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 !

Removing non Work Hours from Excel calculation
 

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)

wrote in message
oups.com...
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 !

Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

ignore - works like a treat now.
thanks for all your help !!!

Removing non Work Hours from Excel calculation
 

ignore - works like a treat now.
thanks for all your help !!!

Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

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

wrote in message
oups.com...
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

Removing non Work Hours from Excel calculation
 

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

Removing non Work Hours from Excel calculation
 

....BUT, if I keep the time at (5,30,0) then the times are correct.Must
be something else in the formula you wrote that needs to be changed.
Thanks again !

Removing non Work Hours from Excel calculation
 

Nope!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
...BUT, if I keep the time at (5,30,0) then the times are correct.Must
be something else in the formula you wrote that needs to be changed.
Thanks again !

Removing non Work Hours from Excel calculation
 

Revise that, yep!

=(NETWORKDAYS(INT(A1),INT(B1))-(WEEKDAY(A1,2)<6)-(WEEKDAY(B1,2)<6))*12.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))*(f1-e1)
+(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)

wrote in message
oups.com...
...BUT, if I keep the time at (5,30,0) then the times are correct.Must
be something else in the formula you wrote that needs to be changed.
Thanks again !

Removing non Work Hours from Excel calculation
 

Hi Linda,

I see Bob's done all the work here but here's a slightly different
version that you could use....

=(NETWORKDAYS(A1,B1)-1)*(F1-E1)+IF(WEEKDAY(B1,2)5,F1,MEDIAN(MOD(B1,1),F1,E1))-IF(WEEKDAY(A1,2)5,E1,MEDIAN(MOD(A1,1),F1,E1))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=520487