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. ## 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. ## 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

Or

=--(0&A1)

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

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

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

You're welcome and thank you for the feedback!

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

Originally Posted by anniea88
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. ## 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. ## 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. ## Re: How do i add leading zeros in hh:mm:ss format?

@ 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.

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

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

#### 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