+ Reply to Thread
Results 1 to 18 of 18

summing / adding times

  1. #1
    Registered User
    Join Date
    03-11-2009
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    9

    summing / adding times

    Hello,

    I've been trying to figure out a way to sum up a column of times like this (please see attached portion).

    Right now, all the cells on the worksheet are formatted as text, and the "total" is actually a text value, not a formula.

    I've tried converting all the cells from text to numeric, even tried custom formats like [h]:mm.ss to no avail.

    What is happening for me is that when I try to sum up a column to get a total, the value gets converted or rounded off to zero (usually like 0:00.00).

    Thanks in advance for your time and consideration on this.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Need help summing / adding times

    Hi,
    is your time minutes, seconds and hundreths of seconds?

  3. #3
    Registered User
    Join Date
    03-11-2009
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Need help summing / adding times

    Yes it is, thanks for the quick response.

    Quote Originally Posted by arthurbr View Post
    Hi,
    is your time minutes, seconds and hundreths of seconds?

  4. #4
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Need help summing / adding times

    What I did was format the cells to HH:MM:SS. You have to re-enter the data after formating the cells to get it to work. The same goes for any time format I believe. You just need to format and then re-enter the data.

    Hope this helps,

    Dan
    Last edited by split_atom18; 04-29-2009 at 03:19 PM. Reason: Removed Example File(New one posted)

  5. #5
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Need help summing / adding times

    Changed to your format. H:MM:SS.00

    Hope this helps,

    Dan
    Last edited by split_atom18; 04-29-2009 at 03:19 PM. Reason: Removed Example File

  6. #6
    Registered User
    Join Date
    03-11-2009
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Need help summing / adding times

    I appreciate the quick reply, and I followed these instructions on my example worksheet. However, what is happening is the cell is rounding up, and I need it to stay at the exact value.

    For example, if I enter 0:01.61, it is getting rounded up to 00:00:02 by following these instructions. How can I prevent the rounding?

    Quote Originally Posted by split_atom18 View Post
    What I did was format the cells to HH:MM:SS. You have to re-enter the data after formating the cells to get it to work. The same goes for any time format I believe. You just need to format and then re-enter the data.

    Hope this helps,

    Dan

  7. #7
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Need help summing / adding times

    Try this one.
    Attached Files Attached Files
    Last edited by split_atom18; 04-29-2009 at 03:20 PM.

  8. #8
    Registered User
    Join Date
    03-11-2009
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Need help summing / adding times

    Thanks, I think I was posting while you were posting

    The second format works without rounding. However, my worksheet has over 900 rows that would need to be re-entered -- is there any other way? Re-pasting the data as values only did not work.
    Last edited by Harmonygirl; 04-29-2009 at 03:21 PM.

  9. #9
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Need help summing / adding times

    I have an idea. Is your data sensitive?

  10. #10
    Registered User
    Join Date
    03-11-2009
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Need help summing / adding times

    Define sensitive? I could upload my times, etc. and show you what I mean, with a little bit of stripping and reformatting on my part. I was just using an attached snippet (see attachment in my original post) as a small example.

  11. #11
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Need help summing / adding times

    Will post again in like 5 mins trying a few things with madeup data.

  12. #12
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Need help summing / adding times

    Like you said you can't copy and paste special values only, however is you copy an item and click in a cell ie make a cursor in there, then paste the data it works. So trying to make a macro to do this for you. I think it might just work. What are the ranges of your cells that need "re-entered"?

  13. #13
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Need help summing / adding times

    Try converting like this (you have to do it a column at a time)

    Select a whole column of data

    Data > Text to columns > Finish

    This converts the text to actual values.

    Now custom format the column as hh:mm:ss.00 and make the last column a sum with the format

    [hh]:mm:ss.00

    Note the square brackets which you need if the sum goes over 24 hours

    Repeat for each column

  14. #14
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Need help summing / adding times

    Worked on my sheet DaddyLongLegs. Nice thinking!

    Here is the differnet data:

    When you have a format of MM:SS.00
    You enter in 00:15.16
    When you select that cell after entering that in in the bar at the top you see: 12:00:15 AM
    If you copy and paste special values only to a general format tab you get: 0.000175462962962963

    Figured this information might help someone at some point.
    Last edited by split_atom18; 04-29-2009 at 03:58 PM. Reason: Added some data

  15. #15
    Registered User
    Join Date
    03-11-2009
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Need help summing / adding times

    Thanks for the idea - I found that when I select the column and then do Data > Text to Columns > Finish, the values are immediately rounded up. Even when I reformat the cells to [hh]:mm:ss.00, my original values are not restored.

    What did I miss?

    Quote Originally Posted by daddylonglegs View Post
    Try converting like this (you have to do it a column at a time)

    Select a whole column of data

    Data > Text to columns > Finish

    This converts the text to actual values.

    Now custom format the column as hh:mm:ss.00 and make the last column a sum with the format

    [hh]:mm:ss.00

    Note the square brackets which you need if the sum goes over 24 hours

    Repeat for each column

  16. #16
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: summing / adding times

    Can you give an example? The numbers might be "rounded" in the formula bar but you should get the correct values in the cells, I think

  17. #17
    Registered User
    Join Date
    03-11-2009
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: summing / adding times

    Hi everyone - sorry for the long hiatus in between answers.

    I am still having this problem on this worksheet, and I now have the time to delve back into it. As a refresher, where I'm at is this:

    Let's say I have a column of time values (this would be a small subset, but is some of the actual data):

    00:00:02.76
    00:00:02.76
    00:00:00.23
    00:00:04.07
    00:00:02.76
    00:00:00.23
    00:00:04.69

    I want to add these so I have a total value that is like this:
    HH:MM:SS.MS (milliseconds)

    I've tried various formats on the column (like Custom formats such as [h]:mm:ss.00) to no avail.

    I should also mention, the data was already inputted into the spreadsheet before I received it to try to format it.

    Any other ideas I can try?

  18. #18
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: summing / adding times

    Sorry, it's the same idea but I think it's the correct one....

    Use text to columns to convert data to numeric. Use a sum formula to total the values at the bottom. format all cells as [h]:mm:ss.00

+ 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