subtract formula
 

i had this formula a few months ago of Bob Phillips,
the purpose of the formula is to remove set times froma n overall
time span

it works fine unless the fault happens after 02:00:00 (in this case
it counts the whole time period
What i want it to do is ignore the times 02:00:00 to 02:30:00

=MOD(D2-C2,1)-((AND(C2<=TIME(2,0,0),(D2=TIME(2,30,0))))+
(AND(C2<=TIME(10,0*,0),(D2=TIME(10,30,0))))+(AND( C2<=TIME(18,0,0),
(D2=TIME(18,30,0))))
+(AND(C2D2,C2<=TIME(10,0,0)))+
(AND(C2D2,C2<=TIME(18,0,0)))+(AND(C2D2,D2*=TIME (2,30,0)))
)*TIME(0,30,0)

any ideas how i can make this happen


thanks

kevin

subtract formula
 

It looks to me to disregard 2:00 to 2:30 Kevin, 9:30 to 4:30 returns 17:30,
19 hours mines 30 mins for 10-10:30, 30 mins for 18-18:30 and 30 mins for
2-2:30.

Give an example where it errors and state what slots it should ignore.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"kevcar40" wrote in message
...

i had this formula a few months ago of Bob Phillips,
the purpose of the formula is to remove set times froma n overall
time span

it works fine unless the fault happens after 02:00:00 (in this case
it counts the whole time period
What i want it to do is ignore the times 02:00:00 to 02:30:00

=MOD(D2-C2,1)-((AND(C2<=TIME(2,0,0),(D2=TIME(2,30,0))))+
(AND(C2<=TIME(10,0*,0),(D2=TIME(10,30,0))))+(AND( C2<=TIME(18,0,0),
(D2=TIME(18,30,0))))
+(AND(C2D2,C2<=TIME(10,0,0)))+
(AND(C2D2,C2<=TIME(18,0,0)))+(AND(C2D2,D2*=TIME (2,30,0)))
)*TIME(0,30,0)

any ideas how i can make this happen


thanks

kevin

subtract formula
 

On 1 Apr, 12:58, "Bob Phillips" wrote:
It looks to me to disregard 2:00 to 2:30 Kevin, 9:30 to 4:30 returns 17:30,
19 hours mines 30 mins for 10-10:30, 30 mins for 18-18:30 and 30 mins for
2-2:30.

Give an example where it errors and state what slots it should ignore.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"kevcar40" wrote in message

...

i had this formula a few months ago of Bob Phillips,
the purpose of the formula is to remove set *times froma n overall
time span

it works fine unless *the fault happens after 02:00:00 (in this case
it counts the whole time period
What i want it to do is ignore the times *02:00:00 to *02:30:00

=MOD(D2-C2,1)-((AND(C2<=TIME(2,0,0),(D2=TIME(2,30,0))))+
(AND(C2<=TIME(10,0*,0),(D2=TIME(10,30,0))))+(AND( C2<=TIME(18,0,0),
(D2=TIME(18,30,0))))
* * * * * * * * * * * * +(AND(C2D2,C2<=TIME(10,0,0)))+
(AND(C2D2,C2<=TIME(18,0,0)))+(AND(C2D2,D2*=TIME (2,30,0)))
*)*TIME(0,30,0)

any ideas how i can make this happen

thanks

kevin

Hi Bob
AS requested i have listed below some of the returns from the
formaula
As you can see if the problem starts after 02:00:00 or ends before
02:30:00 it is not ignoring the time range


Start Time End Time Total Time
01:55:58 02:35:00 00:09:02
01:58:59 02:22:58 00:23:59
01:40:59 02:35:35 00:24:36
02:01:29 02:31:17 00:29:48
01:59:00 02:31:17 00:02:17


thanks for your Help


Kevin