Imported time text to seconds

  • Thread starter Thread starter Literalgar
  • Start date Start date
L

Literalgar

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?
 
Literalgar said:
those entries which could represent a time of day are interpreted
as a time of day in the AM
Click to expand...

.... 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 -----
 
Thank you JoeU2004. I will work with your suggestions, and let you know
(hopefully by the green check!!!!). Many thanks for the time and effort you
put into your response.

JoeU2004 said:
Literalgar said:
those entries which could represent a time of day are interpreted
as a time of day in the AM
Click to expand...

.... 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 said:
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?
Click to expand...
Click to expand...
 
PS....
=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))
Click to expand...

More reliable:

=IF(ISNUMBER(SEARCH(":*:",A1)), --A1,
IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24,
(LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24))


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

JoeU2004 said:
Literalgar said:
those entries which could represent a time of day are interpreted
as a time of day in the AM
Click to expand...

... 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 said:
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?
Click to expand...
Click to expand...
 
Do you also post at the Mr. Excel forum?

There's someone there with a similar user name.

--
Biff
Microsoft Excel MVP


JoeU2004 said:
PS....
=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))
Click to expand...

More reliable:

=IF(ISNUMBER(SEARCH(":*:",A1)), --A1,
IF(LEFT(A1,1)=":", RIGHT(A1,2)/3600/24,
(LEFT(A1,FIND(":",A1)-1)/60 + RIGHT(A1,2)/3600)/24))


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

JoeU2004 said:
Literalgar said:
those entries which could represent a time of day are interpreted
as a time of day in the AM
Click to expand...

... 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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
T. Valko said:
Do you also post at the Mr. Excel forum?
There's someone there with a similar user name.
Click to expand...

Tried to reply in email, but I got a delivery error for that email address.

For personal communication, send email to joeu2004 "at" hotmail.com
 
Hi,

This may give you another idea: Suppose A1 contains the text entry :03 for
3 seconds. Then

=--("00:00"&A1)

will convert this to 12:00:03 AM. You will need to apply a time format to
the cell after entering the formula.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Literalgar said:
Thank you JoeU2004. I will work with your suggestions, and let you know
(hopefully by the green check!!!!). Many thanks for the time and effort you
put into your response.

JoeU2004 said:
Literalgar said:
those entries which could represent a time of day are interpreted
as a time of day in the AM
Click to expand...

.... 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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Shane Devenshire said:
This may give you another idea: Suppose A1 contains the text entry :03
for
3 seconds. Then
=--("00:00"&A1)
Click to expand...

Great idea! Putting it all together:

=IF(ISNUMBER(SEARCH(":*:",A1)), --A1,
IF(LEFT(A1,1)=":", --("00:00"&A1), --("00:"&A1)))


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

Shane Devenshire said:
Hi,

This may give you another idea: Suppose A1 contains the text entry :03
for
3 seconds. Then

=--("00:00"&A1)

will convert this to 12:00:03 AM. You will need to apply a time format to
the cell after entering the formula.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Literalgar said:
Thank you JoeU2004. I will work with your suggestions, and let you know
(hopefully by the green check!!!!). Many thanks for the time and effort
you
put into your response.

JoeU2004 said:
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 -----

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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top