Adding a Stupid Zero...
Posted by Jesse on November 20, 2001 5:32 PM
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? Please help. I want to thank you in advance for your time and consideration on this matter.
Posted by Ivan F Moala on November 20, 2001 11:16 PM
try [hh]:mm:ss
Posted by Ivan F Moala on November 20, 2001 11:18 PM
sorry thats m:ss
Posted by Mark W. on November 21, 2001 6:45 AM
Excel will not interepet :01 as a time value.
It is imported as text. Number formats such
as hh:mm will have no effect on text values.
Suppose that ":01" resides in column A along
with the remainder of your times. Insert a new
column B if necessary and enter the formula,
=IF(LEN(A1)=3,"0:00"&A1,A1)+0 and copy down.
Format column B as hh:mm:ss and repeat this
process for each column of time data. Preserve
these new values using Copy/Paste Special...
Values, and then delete the originally imported
data.
