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?
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?
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
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
Or
=--(0&A1)
Entia non sunt multiplicanda sine necessitate
OMG THANK YOU THANK YOU THANK YOU!! This worked great!
You're welcome and thank you for the feedback!
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
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
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" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks