MrExcel.com - Your One Stop for Excel Tips & Solutions

Formatting Time Values

November 21, 2001 - by

Happy Birthday to MrExcel.com! 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 MrExcel.com 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!