# subtracting a time from a range of times

1. ## subtracting a time from a range of times

new user so sorry if this is simple ,i have been trying to work a formula that will subtract a time ref in one cell from a range of times ie if i arrived at 07:00 am and left at 11:00 but in that period at 08:00 i took a 00:45 min break what time would i be at my destination but not on a break .  Register To Reply

2. ## Re: subtracting a time from a range of times

Maybe have a look here for some ideas:

http://www.cpearson.com/excel/overtime.htm  Register To Reply

3. ## Re: subtracting a time from a range of times

Thanks thats just what i needed  Register To Reply

4. ## Re: Subtracting a fixed time from a range of times

Sorry back again ,i was given a very useful link but the formula i was using doesnt allow for the varying times .if i arrive at my destination at 08:00 and then take a break at 08:15 then start unloading at 09:00 and leave at 10:00 i need to be able to deduct the 00:45 mins to give me the unloading time at store .i was using the formula below .
=((k7-j7+(k7<j7))-(m3-k3+(m3<k3)))*24
any he2lp wou;d be appreciated or point me in the write direction  Register To Reply

5. ## Re: subtracting a time from a range of times

I am not sure I follow.

If you have start time in J7 and end time in K7, then you have break start in K3 and break end in M3, then the formula should give total hours worked.

If it still doesn't work, then could you post a sample of your setup and expected result in a spreadsheet attachment?  Register To Reply

6. ## Re: subtracting a time from a range of times

Thanks for the reply ,the formula works as it you have said my problem is it will deduct the break whatever ,i only need it to deduct that time if it falls within the time period in cells j7 and k7 .i do aplogise i know what i need to do i just dont have the languge to explain my self .i have attached my spreadsheet .th result i am looking for is if the time in cell k3 falls between the time in K7 and N7 the formula will deduct the time in m3 this will then give me the actual tipping time at store .  Register To Reply

7. ## Re: subtracting a time from a range of times

No attachment included.  Register To Reply

8. ## Re: subtracting a time from a range of times  Register To Reply

9. ## Re: subtracting a time from a range of times

I am trying to take your note literally, and I think I understand it as this:

=((K7-J7+(K7<J7))-IF(AND(K7<=K\$3,K\$3<=N7),M\$3,0))*24  Register To Reply

10. ## Re: subtracting a time from a range of times

thanks for the reply the formula works out the time at store but doesnt deduct the break i,ve come up with the following formula ,it seems to work =IF(K3>=K7,(IF(K3<=N7,(N7-K7-M3),0)),0) .how does that look  Register To Reply