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