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
|