+ Reply to Thread
Results 1 to 17 of 17

Adding Positive and negative time values formatted as text

  1. #1
    Registered User
    Join Date
    05-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    17

    Adding Positive and negative time values formatted as text

    Hi,

    I have an issue where I'm trying to add up the time difference for negative and positive values formatted as Text.

    I've attached my sheet and the positive time values return the accurate time difference. However, when a negative value is added there's no output.

    I've also noticed that if I add the positive values together individually I don't get any issues with a total, whereas if I use Autosum for the same section then I get nothing returned (shown in the nov calcs tab).

    appreciate any advice on making this work.

    thanks

    Nick
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Adding Positive and negative time values formatted as text

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or

    Array Formula Requires Ctrl+Shift+Enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Adding Positive and negative time values formatted as text

    Thanks for that, that's resolved my problem with autosum.

    Unfortunately, it doesn't cope with the negative values

    values.JPG

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Adding Positive and negative time values formatted as text

    Array Formula - Requires Ctrl+Shift+Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Adding Positive and negative time values formatted as text

    Hi nixxrite

    Why don,t you bring your time in to sheet Nov Calcs from November 12 as "Real numbers" instead of text!

  6. #6
    Registered User
    Join Date
    05-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Adding Positive and negative time values formatted as text

    Quote Originally Posted by Kevin UK View Post
    Hi nixxrite

    Why don,t you bring your time in to sheet Nov Calcs from November 12 as "Real numbers" instead of text!
    I'm still having trouble with the negative numbers if I pull them in as real.

    I may not have explained myself well or have some formatting issues but please bear with me as I'm relatively new to Excel.

    What I'm expecting to see in L3 is 00:06 and in L4 -00:05 as attached.

    values2.JPG

    I was wondering if there was a better way of doing this rather than converting to text but either way I've gone I've had issues with the negative number.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,689

    Re: Adding Positive and negative time values formatted as text

    Please post a small sample sheet. pictures are nice but ueless

  8. #8
    Registered User
    Join Date
    05-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Adding Positive and negative time values formatted as text

    Quote Originally Posted by Pepe Le Mokko View Post
    Please post a small sample sheet. pictures are nice but ueless
    I did attach the sample sheet to the original post but here's the one I've been playing with since.
    Attached Files Attached Files

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Adding Positive and negative time values formatted as text

    The reason is that we can't type or show the Real Time in negative.

  10. #10
    Registered User
    Join Date
    05-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Adding Positive and negative time values formatted as text

    Quote Originally Posted by :) Sixthsense :) View Post
    The reason is that we can't type or show the Real Time in negative.
    That's probably why I did the text conversion. I couldn't remember the reasoning behind it.

    My brief is that I have a start time of 08:30 some times a person is in beforehand (negative value) and sometimes they're in later (Positive Value). I'm trying to calculate the amount of time lost/gained per person hence the requirement to calculate the daily output as a total unit of time over a period.

    I guess I could do it manually or maybe calculate the negative fields seperate to the positive and do the difference from there somehow

  11. #11
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Adding Positive and negative time values formatted as text

    Hi nixxrite

    Excel doesn't like negative times.

    If i could upload the file i show you how far I got!

  12. #12
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Adding Positive and negative time values formatted as text

    Hi

    I can not upload the file!

    In cell B2 on the Nov Calcs:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And copy across and down. Then in I2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copy down.

    Remember excel doesn't like negative times!

    Kevin

  13. #13
    Registered User
    Join Date
    05-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Adding Positive and negative time values formatted as text

    Thanks Kevin!

    That's exactly what I was looking for

    I had gone down the road of separating negative and positive values to calculate the outcome later but this is much better.

    Thanks again.

  14. #14
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Adding Positive and negative time values formatted as text

    Hi nixxrite

    Thanks for the feed back. apologies about the file, I just can't upload it!

  15. #15
    Registered User
    Join Date
    11-20-2014
    Location
    Mumbai, India
    MS-Off Ver
    2007
    Posts
    2

    Re: Adding Positive and negative time values formatted as text

    Hi,

    I am facing a similar problem but am not being able to figure it out from the solution provided here.

    I am attaching a sample file. An error is being shown in cell E23 because of the negative times.

    Please help me with this.

    Thank you.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Adding Positive and negative time values formatted as text

    Hey arnavkapadia,

    I've had to dig this one out of my archive, I'd stopped using it a while ago. I can't remember exactly where I was going wrong but I believe it may have been something to do with converting to text.

    Hope you don't mind but I re-created your sample on sheet 2 and set it up. It should work how you need it to and you can see how the formula works.Sample edited.xlsx

  17. #17
    Registered User
    Join Date
    11-20-2014
    Location
    Mumbai, India
    MS-Off Ver
    2007
    Posts
    2

    Re: Adding Positive and negative time values formatted as text

    Thank you so much. That was exactly what I was looking for.

+ 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