+ Reply to Thread
Results 1 to 19 of 19

Adding lead zeros to time

  1. 08-03-2009, 03:27 PM #1
    no dice
    no dice is offline
    Forum Contributor
    Join Date
    07-10-2007
    Location
    Portland, OR USA
    Posts
    135

    Adding lead zeros to time

    Hello...

    I'm pasting data from an external source into Excel. The data is in hh:mm:ss format. However, when the source data has zero hours it only displays minutes and seconds preceded by a colon. For example, 5 cells of data in a column might look like this:

    :29:28
    :31:50
    1:15:17
    :05:47
    1:19:36

    I need to run simple calculations on these times, but the cells without an hours value paste into Excel as text format. What are some ways to add a zero before the colon in Excel?

    Example attached.

    Thanks all!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-03-2009, 03:34 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Adding lead zeros to time

    try
    =(0&B2)+0 cell formated hh:mm:sss
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 08-03-2009, 03:38 PM #3
    no dice
    no dice is offline
    Forum Contributor
    Join Date
    07-10-2007
    Location
    Portland, OR USA
    Posts
    135

    Re: Adding lead zeros to time

    Thank you!

    Any way to do it without the helper column?
    Reply With QuoteRegister To Reply
  4. 08-03-2009, 03:49 PM #4
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Adding lead zeros to time

    nope not to my knowledge
    Reply With QuoteRegister To Reply
  5. 08-03-2009, 04:08 PM #5
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Adding lead zeros to time

    You could use a few quick lines of code, eg:

    Please Login or Register  to view this content.
    Above assumes values are in A1 onwards...
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  6. 08-03-2009, 04:25 PM #6
    no dice
    no dice is offline
    Forum Contributor
    Join Date
    07-10-2007
    Location
    Portland, OR USA
    Posts
    135

    Re: Adding lead zeros to time

    Hmm... very interested in that, but afraid I wouldn't know where to insert that code to make it work.
    Reply With QuoteRegister To Reply
  7. 08-03-2009, 04:25 PM #7
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Adding lead zeros to time

    well i could have said that is without code lol
    Reply With QuoteRegister To Reply
  8. 08-03-2009, 04:29 PM #8
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Adding lead zeros to time

    Note, before doing anything make sure you test on a sample file first !
    (Macros can't be undone quite as easily as a normal action)

    If you wanted to use the code... from XL in terms of key presses:

    ALT + F11 -> ALT + I -> M

    copy the code given into the resulting window.

    ALT + F11 (ie back to native XL from VBE) then -> ALT + F8 -> select Example -> Run
    Reply With QuoteRegister To Reply
  9. 08-03-2009, 04:30 PM #9
    no dice
    no dice is offline
    Forum Contributor
    Join Date
    07-10-2007
    Location
    Portland, OR USA
    Posts
    135

    Re: Adding lead zeros to time

    Understood martindwilson! Usually I wouldn't be open to a code solution as the macro alert isn't suitable for my needs. But in this case it works.

    I did try putting DonkeyOte's code in, but it didn't work. Then I realized I forgot to run the macro! lol
    Reply With QuoteRegister To Reply
  10. 08-03-2009, 04:42 PM #10
    no dice
    no dice is offline
    Forum Contributor
    Join Date
    07-10-2007
    Location
    Portland, OR USA
    Posts
    135

    Re: Adding lead zeros to time

    One last bit of help???

    Can we have the code create two leading zeros for cells with no hours?
    Reply With QuoteRegister To Reply
  11. 08-03-2009, 04:46 PM #11
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Adding lead zeros to time

    I don't follow I'm afraid... the code converts all non-time values (text strings) to time .. if you need to see the hours apply a format to the column of hh:mm:ss (if not already)
    Reply With QuoteRegister To Reply
  12. 08-03-2009, 04:50 PM #12
    no dice
    no dice is offline
    Forum Contributor
    Join Date
    07-10-2007
    Location
    Portland, OR USA
    Posts
    135

    Re: Adding lead zeros to time

    Got it... thanks. I thought formatting was being applied by the code. I'm with you now!
    Reply With QuoteRegister To Reply
  13. 08-03-2009, 05:44 PM #13
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Adding lead zeros to time

    alternative code just coz i wanted to see if i could get it to work is select range to change then run this also formats as it goes
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  14. 11-20-2013, 01:46 PM #14
    jfezell
    jfezell is offline
    Registered User
    Join Date
    11-20-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Adding lead zeros to time

    Sorry to reactivate an old thread, but DonkeyOte solution also works great in 2010. Thanks for the help.



    After I posted I noticed one oddity. See attached. Book1.xls

    Row 8, Column A is after I ran the macro. Column B is before. Why did the macro convert 31 hours to 7 hours?
    Last edited by jfezell; 11-20-2013 at 02:03 PM. Reason: Update
    Reply With QuoteRegister To Reply
  15. 11-20-2013, 02:19 PM #15
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Adding lead zeros to time

    because its 24 +7 =31 hours change code to

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  16. 11-20-2013, 02:53 PM #16
    Indi_Ra
    Indi_Ra is offline
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Adding lead zeros to time

    Small modification to martindawilson,
    if time is in this format 01:23:00, code does not recognize the format.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  17. 11-20-2013, 03:37 PM #17
    jfezell
    jfezell is offline
    Registered User
    Join Date
    11-20-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Thumbs up Re: Adding lead zeros to time

    [solution]

    Yep. That fixed it. It now recognizes the format. Thanks.
    Reply With QuoteRegister To Reply
  18. 10-23-2017, 07:47 AM #18
    priyanka234822
    priyanka234822 is offline
    Registered User
    Join Date
    10-23-2017
    Location
    Delhi
    MS-Off Ver
    2010
    Posts
    1

    Re: Adding lead zeros to time

    is there any excel formula for adding zero to time.
    I have data in text format like:
    7:05
    5:12:10
    3:45
    I need to convert this data into hh:mm:ss and sum it. where 7:05 is mm:ss and 5:12:10 is hh:mm:ss
    Reply With QuoteRegister To Reply
  19. 10-24-2017, 02:53 AM #19
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Adding lead zeros to time

    priyanka234822 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1