"Literalgar" wrote:
those entries which could represent a time of day are interpreted
as a time of day in the AM

.... or PM, depending on the numbers. But whenever you have two colons
(xx:xx:xx, x:xx:xx or 0:xx:xx), the data is indeed interpreted as hh:mm:ss.
You can see this by formatting the cells with the Custom format "[h]:mm:ss"
without quotes.

The real problem arises with imported times of the form xx:xx or 0:xx
(interpreted as hh:mm) and :xx, which is interpreted as text because it is
not a valid time form.

There are adjustments that you can make to each form after importing. For
xx:xx and 0:xx, you could use copy-and-paste-special-divide 60. And for
:xx, you could use a formula of the form =RIGHT(A1,2)/3600/24, and
copy-and-paste-special-"value and number formats" to overwrite :xx. In both
cases, format the resulting cell with the same Custom format above.

But I doubt that that is the general solution you are looking for, since it
requires that you be mindful of the original form or that you recognized the
incorrect results and handle them individually.

Perhaps a better solution for you would be to import the time field in Text
format, not General. (You can do this using the Import Wizard.) Then use a
macro to convert the forms. If you are interested in the details of the
macro, indicate that in a response to this thread.

Alternatively, use a formula of the following form in a parallel column of
helper cells:

=IF(LEN(A1)5, --A1,
IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24,
(LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24))

Format those cells with the Custom format above, and copy-and
paste-special-"value and number formats" to overwrite the original text
cells. Then you can delete the helper cells.

Note: The formula above assumes that you do __not__ have data of the form
1:2:3, meaning 1h 2m 3s. Your original posting suggests that the form of
that data would be 1:02:03 instead.


----- original message -----

"Literalgar" wrote in message
...
I am importing time data, which imports in the hh:mm:ss format (or omitting
hh and mm if 0 value) to my eyes, but in the general format, so those
entries
which could represent a time of day are interpreted as a time of day in
the
AM, and that which could not be a time of day is interpreted as text.

e.g. "4:00" in a cell shows as "4:00 AM" in the formular bar and :40
shows
as :40 both places, but is formatted as text.

Prior to import, and to the eyes after import in the cells themselves,
there
are five types of entries:
00:00:00
0:00:00
00:00
0:00
:00

How can I convert these text strings and time values to seconds? Can it
be
done?