subtract formula

[kevcar40]

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(C2>D2,C2<=TIME(10,0,0)))+
(AND(C2>D2,C2<=TIME(18,0,0)))+(AND(C2>D2,D2>­=TIME(2,30,0)))
)*TIME(0,30,0)

any ideas how i can make this happen


thanks

kevin

 

[Bob Phillips]

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)


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(C2>D2,C2<=TIME(10,0,0)))+
(AND(C2>D2,C2<=TIME(18,0,0)))+(AND(C2>D2,D2>­=TIME(2,30,0)))
)*TIME(0,30,0)

any ideas how i can make this happen


thanks

kevin

 

[kevcar40]

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)



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(C2>D2,C2<=TIME(10,0,0)))+
(AND(C2>D2,C2<=TIME(18,0,0)))+(AND(C2>D2,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