# 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 .

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

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

Thanks thats just what i needed

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

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?

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 .

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

No attachment included.

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1