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)

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

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)

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

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)

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

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)

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

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)

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

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

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)

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 !

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

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

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

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)

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

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

....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 !

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 !

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 !

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