Subtraction formula
On 31 Jan, 19:00, "Bob Phillips" wrote:
This looks better Kevin
=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<=TIM E(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)
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"kevcar40" wrote in message
...
On 31 Jan, 14:56, kevcar40 wrote:
On 31 Jan, 14:13, "Bob Phillips" wrote:
Kevin,
Can you give me an example that errors. I tried it with your examples,
including 08:25-04:25 and got the same results as you predicted.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"kevcar40" wrote in message
....
On 31 Jan, 10:24, "Bob Phillips" wrote:
This seems to work
=MOD(D20-C20,1)-((AND(C20<=TIME(2,0,0),(D20=TIME(2,30,0))))+(AND( C20<=TIME****(10,0,0),(D20=TIME(10,30,0))))+(AND( C20<=TIME(18,0,0),(D20=TIME(18,30,*0*)*)*))
+(AND(C20D20,D20=TIME(2,30,0))) )*TIME(0,30,0)
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"kevcar40" wrote in message
...
Hi
I have a worksheet containing time faults occur
ie
Start time End Time
06:23:12 07:23:12
08:10:23 12:10:23
08:25:10 04:25:10
what i am looking to do is subtract the start time from the end time
and exclude break times
ie
break 1 10:00:00 to 10:30:00
break 2 18:00:00 to 18:30:00
break 3 02:00:00 to 02:30:00
End Time Start time
07:23:12 - 06:23:12 = 01:00:00
12:10:23 - 08:10:23 = 03:30:00
19:15:00 - 15:15:20 = 03:30:00
20:10:23 - 08:10:23 = 09:00:00
04:25:10 - 08:25:10 = 18:30:00
any ideas on a formula
thanks
kevin- Hide quoted text -
- Show quoted text -
Bob
Thanks for Formula
I have a slight ploblem with it
the formula works fine unitl the end time goes past 00:00:00
then it returns wrong answer it is out by 1 hour
it works fine when both start and end time are after 00:00:00
i have played with formula but getting nowhere
any ideas?
thanks
kevin- Hide quoted text -
- Show quoted text -
Bob
I have tried this data
Start time End time * * Result Expected Result
09:30:30 12:30:00 * 2:30:00 (3 - 30) 2:30:00
09:30:00 19:30:00 * 9:00:00 (10-1) 9:00:00
09:30:00 04:30:00 * 18:30:00 (19-1:30) 17:30:00
sorry text wrapped hope is better
kevin- Hide quoted text -
- Show quoted text -
Thank you Bob for all your help
Works a treat
thanks again
kevin
|