+ Reply to Thread
Results 1 to 10 of 10

subtracting a time from a range of times

  1. #1
    Registered User
    Join Date
    04-15-2010
    Location
    northampton
    MS-Off Ver
    Excel 97-2000
    Posts
    9

    Post 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 .
    Last edited by coatey1; 04-16-2010 at 12:08 PM. Reason: my mistake not sloved

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: subtracting a time from a range of times

    Maybe have a look here for some ideas:

    http://www.cpearson.com/excel/overtime.htm
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-15-2010
    Location
    northampton
    MS-Off Ver
    Excel 97-2000
    Posts
    9

    Re: subtracting a time from a range of times

    Thanks thats just what i needed

  4. #4
    Registered User
    Join Date
    04-15-2010
    Location
    northampton
    MS-Off Ver
    Excel 97-2000
    Posts
    9

    Post 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. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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. #6
    Registered User
    Join Date
    04-15-2010
    Location
    northampton
    MS-Off Ver
    Excel 97-2000
    Posts
    9

    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. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: subtracting a time from a range of times

    No attachment included.

  8. #8
    Registered User
    Join Date
    04-15-2010
    Location
    northampton
    MS-Off Ver
    Excel 97-2000
    Posts
    9

    Re: subtracting a time from a range of times

    sorry about the delay ,will try again to add spreadsheetBook2.xls

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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. #10
    Registered User
    Join Date
    04-15-2010
    Location
    northampton
    MS-Off Ver
    Excel 97-2000
    Posts
    9

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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