seconds not formatted as time

[Marcia]

I have an Excel 2000 spreadsheet where data was copied over from a call
tracking system. The call tracking system only keeps track of 2 months of
data so we're not able to request the call reports again; otherwise, we'd
just request them again in the format needed.

One of the columns of data indicates time which should be in minute:second
format. However, the call tracking entered the number of seconds as :10 for
example. Excel will not recognize this as time because it is missing a 0 in
front of the colon, for example 0:10. If the cell has a minute and second
(for example 1:10), Excel recognizes it as time. Is there an easy way to
add 0 in front of the cells that do not have a minute?

Example of data:

7:00 :00
7:30 :00
8:00 :13
8:30 1:01


Thanks,
Marcia

 

[Mark Rosenkrantz]

Marcia;

Yes there is, but I can't give you the exact format fot it as I have to look
at it.
Bottom line is that you can give every cell a special format.

Select the cell, go to Format Ceels, click the tab, number ans select the
custom category.
In the Type editbox format your on string.

You can enter formats for positive values, negative values, zeros and text
( in that order ).
The formats need to be divided by a semicolon. ( ; ).
Probably someone comes up with the exact format, but this is to give you a
starter.

Succes;

Mark.

More Excel ? www.rosenkrantz.nl or (e-mail address removed)

 

[Ron Rosenfeld]

I have an Excel 2000 spreadsheet where data was copied over from a call
tracking system. The call tracking system only keeps track of 2 months of
data so we're not able to request the call reports again; otherwise, we'd
just request them again in the format needed.

One of the columns of data indicates time which should be in minute:second
format. However, the call tracking entered the number of seconds as :10 for
example. Excel will not recognize this as time because it is missing a 0 in
front of the colon, for example 0:10. If the cell has a minute and second
(for example 1:10), Excel recognizes it as time. Is there an easy way to
add 0 in front of the cells that do not have a minute?

Example of data:

7:00 :00
7:30 :00
8:00 :13
8:30 1:01


Thanks,
Marcia

Actually, your 1:10 (or 1:01 in your example), although recognized as time,
will be recognized as one hour ten minutes; not as 1 minute 10 seconds.

So you have to figure out what's there, and then convert it differently
depending on what you see.

If what you show are the only two formats in which time has been entered, then
the following formula should change the contents to minutes and seconds.

=--IF(ISTEXT(B1),"0"&LEFT(B1,FIND(":",B1)-1) & ":" &
MID(B1,FIND(":",B1)+1,255),B1/60)

Format the result with a time format. Something like [h]:mm:ss might be
appropriate. Having the [h] also lets you double check that the conversion
went OK to minutes and seconds.

Do this first on a copy of your worksheet. Since you can't get the original
data anymore, I would work only from copies.


--ron