Subtraction formula

K

kevcar40

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
 
B

Bob Phillips

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(C20>D20,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)
 
K

kevcar40

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(C20>D20,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)









- 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
 
B

Bob Phillips

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)



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(C20>D20,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)









- 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
 
K

kevcar40

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)






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
 
K

kevcar40

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
 
B

Bob Phillips

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<=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)

--
---
HTH

Bob


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



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
 
K

kevcar40

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<=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)

--
---
HTH

Bob

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





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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pls help for Simple Calculation 1
Formatting time 2
Determine if night shift by start & end time 2
Formating time 2
vlookup or Match 6
Sumproduct 1
Formatting Date Field 5
UNABLE TO SORT DATE 1

Top