Closed Thread
Results 1 to 10 of 10

How do i add leading zeros in hh:mm:ss format?

  1. #1
    Registered User
    Join Date
    03-07-2014
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    How do i add leading zeros in hh:mm:ss format?

    I am trying to add a leading zero to sum up the total time spent on a project. the data came back in this format :00:00 which does not allow me to sum up. I changed the format to HH:MM:SS but that did not add the zero. any thoughts?

  2. #2
    Registered User
    Join Date
    03-07-2014
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    21

    Re: How do i add leading zeros in hh:mm:ss format?

    To sum the passage of time use this time format 37:30:55. You'll see it in the format cells dialog box in the number tab in the time category. You can launch the dialog box from the number group on the home tab.

    HansensOffice.com

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How do i add leading zeros in hh:mm:ss format?

    Hi and welcome to the forum

    Try this formula

    =--IF(LEFT(A1)=":","00"&A1,A1)

    Format cell Custom

    hh:mm:ss
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How do i add leading zeros in hh:mm:ss format?

    Or

    =--(0&A1)
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    03-07-2014
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: How do i add leading zeros in hh:mm:ss format?

    OMG THANK YOU THANK YOU THANK YOU!! This worked great!

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How do i add leading zeros in hh:mm:ss format?

    You're welcome and thank you for the feedback!

  7. #7
    Forum Contributor
    Join Date
    01-25-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    434

    Re: How do i add leading zeros in hh:mm:ss format?

    Quote Originally Posted by anniea88 View Post
    OMG THANK YOU THANK YOU THANK YOU!! This worked great!
    just for reference, out of the two solutions that was provided, which one worked best for you ?

  8. #8
    Registered User
    Join Date
    10-22-2021
    Location
    Chennai, India
    MS-Off Ver
    365
    Posts
    1

    Re: How do i add leading zeros in hh:mm:ss format?

    Hi All, This formula in italics IF(LEFT(A1)=":","00"&A1,A1) works by adding leading zeros when the data is in the format of :01:02. However it brings a new challenge while applying other functions. Even after changing the format cell customer to hh:mm:ss after adding leading zeros, I am only able to add them using + operator as none of the other functions work on this (Ex Sum, Average is not working on this). any help here please

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,040

    Re: How do i add leading zeros in hh:mm:ss format?

    First of all welcome!

    This thread is 7 years old, you should really be starting your own thread, to get help on your problem. If you include a sample workbook in line with the yellow banner and describe your problem, and what you want the solution to look like manually I'm sure someone can help you

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    Re: How do i add leading zeros in hh:mm:ss format?

    Administrative Note:

    @ Mahiprabhu

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Leading Zeros lost in csv format
    By Scotsman89 in forum Excel General
    Replies: 18
    Last Post: 04-05-2012, 04:04 AM
  2. Format for Leading Zeros
    By DavidW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2006, 04:01 PM
  3. Format a cell to keep leading zeros.
    By Shadyhosta in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-27-2005, 11:37 AM
  4. Replies: 1
    Last Post: 05-04-2005, 02:06 PM
  5. leading zeros in text format
    By BigBrook in forum Excel General
    Replies: 3
    Last Post: 02-04-2005, 08:06 PM

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