+ Reply to Thread
Results 1 to 9 of 9

How to subtract a negative time value (HH:MM) from a running total?

  1. #1
    Registered User
    Join Date
    09-25-2009
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    40

    Cool How to subtract a negative time value (HH:MM) from a running total?

    Hello, Excel Experts.

    I have two running totals. One is purely numbers and usually has positive values, but occassionally negative ones. The running total will easily handle the negative values, just by subtracting them from everything else to date.

    The second is in hours and minutes (HH:MM), however. Occasionally there will also need to be a reduction in the total time - but how do I represent and calculate this in a similar way?

    (Rep awaits for those I haven't already given it to recently, much as I would love to dole some out to them again )

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,504

    Re: How to subtract a negative time value (HH:MM) from a running total?

    Can I assume that by asking this question that MSFT has not yet fixed the negative time bug in Excel? Have you tried simply entering your negative time (-0:50:0) and copying the formula from G12 in H12? If you are not required to be Excel compatible, other spreadsheets (LO Calc or Google Sheets) have long been able to handle negative times just fine. In LO Calc, I can simply enter my negative time in H11 (-0:50:0) and the formula in H12 is simply =G12+H11

    If you must be Excel compatible, are you required to be able to read the negative value in H11? I can enter =CONVERT(-50,"mn","day") (or do the unit conversion on a hand calculator and enter the result) into H11. If the cell is formatted as time, Excel will show #######, but the underlying value is still valid, and I can still add to the previous day's running total =G12+H11. If I format the cell as general, I can see the value in H11 as decimal days.

    If I want to enter decimal minutes (like in row 5), I could use a formula in row 11 to convert all of those decimal minutes values to days. =CONVERT(D5,"mn","day") [copy/paste/fill into D11:H11). H11 will still be unable to show the negative time, but I can refer to row 5 to understand what value is represented in row 11.

    If it is required to be able to read row 11 as sexagessimal time, I might add a "add/subtract" toggle to row 10. I enter the positive times in row 11. Where I want to subtract the time in row 11 instead of add, I enter -1 into row 10. The formula in row 12 then becomes =C12+PRODUCT(D10:D11). With that change, I can enter -1 into H10, 0:50:0 into H11, and the formula in H12 will subtract the time as indicated.

    Will any of those solutions fit into how you work with this spreadsheet?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    09-25-2009
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    40

    Re: How to subtract a negative time value (HH:MM) from a running total?

    Well I'm not sure exactly what the negative time bug is, but if it means that you can't seem to take a time value and add a negative symbol to it at the beginning.. then no, they've not fixed that! Especially unfortunate as, yes - I am required to be Excel compatible.

    The sheet is something that others will need to be able to both enter and read input values into, so it does get even trickier.. I think your last solution is the most elegant one and I will go away and try to implement it. Thank you so much and please enjoy your latest rep!

  4. #4
    Registered User
    Join Date
    09-25-2009
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    40

    Re: How to subtract a negative time value (HH:MM) from a running total?

    Uh oh.. I just tried the last solution and am getting an error. I don't know if it's anything obvious, and it seemed like this should work, but something's up. Should this definitely be working? Either way, I still appreciate all your thoughts on this! Subtracting Negative Time from Running Total - MrShorty.xlsx

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,504

    Re: How to subtract a negative time value (HH:MM) from a running total?

    Yes, that is the "negative time bug" that I am referring to, and I've started calling it a bug because other spreadsheets have long resolved this problem and I don't know why MSFT has not fixed this in Excel. It's really unfortunate that you must be Excel compatible, because this is a lot easier in other spreadsheets.

    Yesterday's example led me to believe that the result in row 12 would always be positive. You would never subtract a value large enough to make row 12 be less than 0. In today's example, H11 is 1:45, but G12 if 1:40, resulting in -00:05 in H12, but Excel cannot display negative times in the requested sexagessimal format. In order to make these readable, we have to convert the numbers to text strings (something I generally avoid unless absolutely necessary). Here's how I would proceed:

    1) First recognize that the underlying values in row 12 are correct, even when Excel cannot display them. H12 has the correct result. I would be inclined to leave row 12 alone if at all possible. You can leave the number formatting alone, or format row 12 as general so you can see the decimal days number even when the number is negative. I would also format row 10 as general, since the values in row 10 are not representing time values and you will want to be able to see the -1 values where entered.
    2) In row 13, I would add a number to text formula that will provide the readable version of what is in row 12. Something like =IF(C12<0,"-","")&TEXT(ABS(C12),"[hh]:mm") in C13 and copy/paste/fill across.

    The hardest part of this approach is remembering that row 13 is a dead end calculation. Row 13 is "for display only." You don't want to try to use these text strings in other calculations. If you have other calculations to perform on these times, use the value in row 12. Which is probably especially difficult if you have to communicate that to other users who will use this spreadsheet.

  6. #6
    Registered User
    Join Date
    09-25-2009
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    40

    Re: How to subtract a negative time value (HH:MM) from a running total?

    MrShorty.. please have my apologies for a very stupid mistake here. It's absolutely true that H12 will always be positive (or at least, not less than 0).

    Forgetting this, to test your reply I'd simply put any old random number in to H11 without thinking about what the actual output would be. Of course 01:45 would make the running total negative from what was already there!

    Now that I've reduced H11 to something more sensible - yes, this completely works and is what I'll use going forward. Thank you sincerely for the solution here and I'll mark the thread complete!

  7. #7
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: How to subtract a negative time value (HH:MM) from a running total?

    Alternatively, type in the minutes as per row 5 (i.e. 35 instead of 00:35), and change the formula in D12 to: =C12+TIME(0,ABS(D11),0)*SIGN(D11)

  8. #8
    Registered User
    Join Date
    09-25-2009
    Location
    London, England
    MS-Off Ver
    MS Office 365
    Posts
    40

    Re: How to subtract a negative time value (HH:MM) from a running total?

    Got it - I guess that'd work as long as I'm comfortable having my users enter any changes as minutes instead of hours and minutes. It'd be less consistent with how time is represented everywhere else in the workbook, but would allow the minus sign to be used. I've now got two approaches I can consider. Thanks again, both! Great forum as always.

  9. #9
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: How to subtract a negative time value (HH:MM) from a running total?

    You are welcome and thanks for the Rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Running total that outputs 0 if the sum is negative and applying max caps
    By George Davis in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-06-2023, 06:35 AM
  2. [SOLVED] Need to fix formula so running total in HR Remaining column is not a negative number
    By Sunny625 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-04-2017, 02:18 AM
  3. Replies: 8
    Last Post: 07-19-2017, 12:06 PM
  4. subtract from running total
    By Tanabar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-24-2014, 11:22 AM
  5. running total subtract % of previous months
    By amartino44 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-17-2013, 01:42 PM
  6. sum and subtract time at the same time from a running total
    By simonood in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2013, 02:10 AM
  7. Replies: 2
    Last Post: 01-18-2013, 11:47 AM

Tags for this Thread

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