Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Subtracting times

This is a discussion on Subtracting times within the Excel Questions forums, part of the Question Forums category; I need to subtract one cell from another. I want to subtract an input value (minutes) from a cell that ...

  1. #1
    New Member
    Join Date
    May 2008
    Posts
    49

    Default Subtracting times

    I need to subtract one cell from another. I want to subtract an input value (minutes) from a cell that contains a calculated value of hours & minutes. The calculated cell is custom formated h:mm. I prefer the input be a simple numeric value. The answer needs to be in the h:mm. How do I do this?

  2. #2
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,706

    Default Re: Subtracting times

    Excel manages time as fractions of a 24-hour day. So, divide the number of minutes entered by 24*60 (1440), and subtract that from the original h:mm, then format the result cell as h:mm.
    More specifically:
    Assume your h:mm cell you're subtracting from is in A2 and the number of minutes to subtract from it is in B2, with the answer in C2. Format C2 as h:mm, then enter the following formula into C2:
    Code:
    =A2-(B2/1440)
    Hope this helps,
    Cindy
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    May 2008
    Posts
    49

    Default Re: Subtracting times

    I formatted the cell custom h:mm
    When I enter 50, it displays in the cell as 0:00 and
    It displays in the formula bar 2/19/1900 12:00:00 AM

    What's going on?

    THX,

    Dale

  4. #4
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,706

    Default Re: Subtracting times

    I don't know if this will be any help, but here's what my spreadsheet looks like:


    Microsoft Excel - mrexcel 5.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =


    A
    B
    C
    D
    1
    End TimeElapsed MinutesResult
    2
    4:15163:59
    3
    A2 format is h:mmB2 is generalC2 is h:mm
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.




    ******** ******************** src="
    Row 3 indicates the formats of each of the cells in Row 2.
    If you format B2 as h:mm, Excel will treat the entry as the number of days since Jan 1, 1900 rather than as the number of minutes to subtract from the value in column A.
    Hope this helps,
    Cindy

  5. #5
    New Member
    Join Date
    May 2008
    Posts
    49

    Default Re: Subtracting times

    Cindy,

    Thank you so much for your assistance. Everything you have told me to do has worked. However, I am presented with another problem.

    These are actually driving times for house cleaning personnel. We use Google maps to calculate "estimated" driving time. We use clock-in and clock-out times to determine "actual" driving times. More often than not the "actual" driving time is greater than the "estimated" driving time.

    The formula scheme that you provided works wonderfully if the "actual" time is less than or equal to the "Google estimate". However, when it is greater (which is typical) the result is ########.

    I apologize if I described my original problem incorrectly. Can you still help?

    THX

    Dale

  6. #6
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,706

    Default Re: Subtracting times

    Definitely a bit more complicated
    To get to the best solution as quickly as possible, can you clarify a couple of things?
    1. Is the "Estimated Time" (let's call this A2) a clock time (i.e. estimated arrival time) or a duration (i.e. estimated driving time)? (or something else)
    2. Is the Estimated Time entered directly or calculated? Your original post indicated it was calculated...if so, what is it being calculated from?
    3. Is the cell that is being subtracted from the estimate always in raw minutes (so 1 hour 15 minutes would be entered as 75)?
    4. Is the result of the subtraction a clock time or a duration (actual arrival time vs. actual driving time)? If it's elapsed time, could it be calclated as just minutes (75 minutes) , rather than hours and minutes?
    I know these may seem irrelevant, but Excel by default treats all time as clock time, so it can't handle a negative number formatted with it's built-in formats. There IS a way to do what you need, but it will be more (or less) complicated depending on your answers.
    Cindy
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    May 2008
    Posts
    49

    Default Re: Subtracting times

    Cindy,

    It's terrible when you're not even smart enough to ask the right question.

    Allow me to explain a little more about my situation. I have programmers in India that are writing SQL website applications. I am creating the application criteria for them to show what the GUI website applications software will need to do when we go "live".

    I mention this so you will understand where the data is coming from in order to answer your question. However, this mockup Excel file will have nothing to do with the website application, of course. Therefore, all I need to do for my programmers is to illustrate the concept so they can readily see what kind of magic they need to do in SQL. What I am writing and what they will eventually write has no relationship except for illustrative purposes.

    That said, the estimated driving time will ultimately be a time that is ascertained from a Google maps calculation. For my Excel application, however, it can simply be a manual input. These will be fictitious numbers anyway.

    The actual driving time, however, will be calculated. I enter a fictitious "in time" and a fictitious "out time" and Excel calculates the difference in a h:mm formatted cell. The SQL application will use "clock times" for the in times and out times in order to determine lapsed times.

    Note: in the real world, because the cleaning staff takes time to load and unload their cars, the actual driving time will always appear to be greater than the Google maps calculated time.

    I will be glad to answer your questions specifically, however, I thought, perhaps, you needed to see the bigger picture before I did so. Just let me know if you want me to answer them as is or if this provides adequate information.

    THX,

    Dale

  8. #8
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,706

    Default Re: Subtracting times

    Sorry for the delay...but I think the table below will give you some examples of options. If you click a cell with an underline, you can see the formula that created it.

    Columns A and B are formatted with AM/PM, because they represent clock time, and for the time arithmetic to work correctly, Excel needs to know that 4:00 is 4 PM rather than 4 AM. If you're entering times from a 24-hour clock, then this distinction would not be necessary, and the times could be entered without the AM/PM disinction. (If any of your elapsed times go across midnight, however, this won't work as written).
    Column C is formatted as h:mm, and is the elapsed time between A and B. The assumption here is that these will always be positive numbers, since they represent elapsed time.
    Columns D, E, and F are formatted as numbers with no decimals.
    Column D represents the same information as column C, but is converted to minutes rather than hours and minutes. This is provided just as an optional way of displaying the data (you get to choose what your requirements really are).
    Column E is the estimated driving time in minutes, as derived from Google maps or whatever other estimation system you might have.
    Column F is the difference between actual and estimated, displayed as minutes (just as an option).
    Column G looks like it's formatted as h:mm. It's not...it's a text representation of the hours and minutes, and can't be used in calculations as-is. This was to allow the time to show up as a negative, since Excel will not display a negative time.
    I hope some of this is useful to you, at least as a tool for communication with your offshore developers.
    Cindy


    ******** ******************** ************************************************************************>
    Microsoft Excel - mrexcel 5.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    1
    In TimeOut timeElapsed TimeElapsed time
    in minutes
    Est. Driving
    Time
    Difference
    (Actual - est.)
    in minutes
    Difference
    formatted to
    look like time
    2
    8:00 AM9:15 AM1:157562130:13
    3
    8:15 AM11:00 AM2:4516578871:27
    4
    8:30 AM1:00 PM4:30270252454:05
    5
    8:45 AM12:15 PM3:30210240-30-0:30
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    May 2008
    Posts
    49

    Default Re: Subtracting times

    Cindy,

    Your answer was exactly what I was looking for. Your example demonstrates that you perfectly grasped the concept.

    But there's one small problem...

    The formulas do not display when I click on the underscored fields. They act as a hyperlink and simply return to the message board. I tried it several times and it responded the same way each time.

    If you could provide the formulas in some other manner -- you will have solved my problem for which I am deeply grateful.

    Thanks Again,

    Dale

  10. #10
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,706

    Default Re: Subtracting times

    Dale,
    I gave the wrong instructions for seeing the formulas. There's a small dropdown to the left of the formula bar. You can choose each cell individually to see the formula. I thought the "links" did the same thing, but I may have pasted in the code for the worksheet incorrectly.
    Hope this is what you need.
    Cindy
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com