About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store



Past Tip of the Day


Happy Birthday to! Three years ago today, this site was launched. It took four days before the first two questions came in. I eagerly opened them both up and had no clue how to answer either one. I thought, "Oh no - maybe this was a mistake...".

Since then, traffic has picked up tremendously. I am about to hit my one-millionth page view for the year 2001. There is enough consulting work to keep myself busy, plus more projects for Juan Pablo and Mala and occassionally Access projects for Mike and data analysis for John.

People are simply amazed when they see what can be done with Excel/VBA. For all of this, I am very thankful. Tomorrow is Thanksgiving in the United States, and I would like to offer my thanks to the loyal readers & clients who have made a success.

Todays question: My question sounds simple but I cannot figure this out. I work at a Call Center and I'm importing info from reports created by the Avaya System (phone system). Now, this reports have talk time, wrap up time, and other stats.
When I import the stats to Excel if the time is less than 1 min it appears as :01 sec. What I need is for Excel to change this to 0:01. The main reason I need that zero is because when I transfer this info to Access it does not recognize anything that does not appear in this format (0:00). I have played with the Formatting and have yet to figure this out. It is very time
consuming to sit there and add zeros to fix this! My knowledge of Excel is very basic. Can you build a macro? Or what about conditional formatting?

I would use this simple macro. First, highlight the selection with the times, then run the macro.

Sub TryThis()
    For each cell in Selection
        if left(cell.value, 1) = ":" then
            cell.value = "0" & cell.value
        end if
    Next cell
End Sub

For all of our U.S. readers, have a great Thanksgiving weekend!

By Bill Jelen on 21-Nov-2001 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.