Can't stop time duration [hh]:mm:ss converting to time of day
 

I am trying to import an excel spreadsheet with 12000+ records to access but
can't stop a formatting problem for a time duration field. The data appears
correctly in Excel cells as E.g. 00:17:33 but in the formula bar as "12:17:33
AM" and the latter is what ends up in the Access database. I have unlocked
the cell format protection, have reformatted the column using cell format to
[hh]:mm:ss or [mm]:ss and the format changes will appear in the data in the
column but still appears in the formula bar as 12:17:33 AM. It is apparent
that Excel is interpreting the entry as a time of day rather than a duration.
How can I stop this? (As I mentioned there are over 12,000 records in the
spreadsheet so PLEASE don't suggest anything that requires reentering all the
data :).]

Hi
you may try using a helper column with the formula
=TEXT(A1,"[hh]:mm")


--
Regards
Frank Kabel
Frankfurt, Germany
"Jack Cutrone" <Jack schrieb im
Newsbeitrag ...
I am trying to import an excel spreadsheet with 12000+ records to access
but
can't stop a formatting problem for a time duration field. The data
appears
correctly in Excel cells as E.g. 00:17:33 but in the formula bar as
"12:17:33
AM" and the latter is what ends up in the Access database. I have
unlocked
the cell format protection, have reformatted the column using cell format
to
[hh]:mm:ss or [mm]:ss and the format changes will appear in the data in
the
column but still appears in the formula bar as 12:17:33 AM. It is
apparent
that Excel is interpreting the entry as a time of day rather than a
duration.
How can I stop this? (As I mentioned there are over 12,000 records in the
spreadsheet so PLEASE don't suggest anything that requires reentering all
the
data :).]

Assuming you have windows, go to startcontrol panelregional options/settings
change the time format to HH:mm:ss (24 hour) as opposed to hh:mm:ss (12 hour)

Regards,

Peo Sjoblom

"Jack Cutrone" wrote:

I am trying to import an excel spreadsheet with 12000+ records to access but
can't stop a formatting problem for a time duration field. The data appears
correctly in Excel cells as E.g. 00:17:33 but in the formula bar as "12:17:33
AM" and the latter is what ends up in the Access database. I have unlocked
the cell format protection, have reformatted the column using cell format to
[hh]:mm:ss or [mm]:ss and the format changes will appear in the data in the
column but still appears in the formula bar as 12:17:33 AM. It is apparent
that Excel is interpreting the entry as a time of day rather than a duration.
How can I stop this? (As I mentioned there are over 12,000 records in the
spreadsheet so PLEASE don't suggest anything that requires reentering all the
data :).]

Jack Cutrone wrote:

It is apparent
that Excel is interpreting the entry as a time of day rather than a
duration.
How can I stop this?

It's a fundamental problem. Time is in continuum rather than a series
of discrete values. Excel lacks an interval data type. You should model
duration as the period between two DATETIME values i.e. use two columns
e.g. start_date and end_date respectively.

there are over 12,000 records in the
spreadsheet so PLEASE don't suggest anything that requires reentering
all the
data :)

Oh. Forget I mentioned anything ;-)

Jamie.

--

Hi -
I tried both the solutions suggested by both Frank Kabel and Peo Sjoblom and
they both worked.

I opted for the solution suggested by Frank. With Peo's solution, when I
did the change the time settings, did the conversion and that worked, but
then, since I was not sure, but didn't think I wanted to keep the time
setting change, as soon as I changed it back, the table reverted to having
the "12:XX:XX AM" problem.

Thanks for all the posts from a neophyte Excel user. I was pulling my hair
out last night, and you certainly solved the problem for me.

Jack Cutrone

i am fighting the same issue and appreciate your continued help.

i do not know how to implement this solution, could you please expand your
directions?

thanks.
ross hoek

"Frank Kabel" wrote:

Hi
you may try using a helper column with the formula
=TEXT(A1,"[hh]:mm")


--
Regards
Frank Kabel
Frankfurt, Germany
"Jack Cutrone" <Jack schrieb im
Newsbeitrag ...
I am trying to import an excel spreadsheet with 12000+ records to access
but
can't stop a formatting problem for a time duration field. The data
appears
correctly in Excel cells as E.g. 00:17:33 but in the formula bar as
"12:17:33
AM" and the latter is what ends up in the Access database. I have
unlocked
the cell format protection, have reformatted the column using cell format
to
[hh]:mm:ss or [mm]:ss and the format changes will appear in the data in
the
column but still appears in the formula bar as 12:17:33 AM. It is
apparent
that Excel is interpreting the entry as a time of day rather than a
duration.
How can I stop this? (As I mentioned there are over 12,000 records in the
spreadsheet so PLEASE don't suggest anything that requires reentering all
the
data :).]

Hi
if your date values are in column A, enter this formula in B1 and copy down
for all rows. Now import column B in your database

--
Regards
Frank Kabel
Frankfurt, Germany
"hoeker" schrieb im Newsbeitrag
...
i am fighting the same issue and appreciate your continued help.

i do not know how to implement this solution, could you please expand your
directions?

thanks.
ross hoek

"Frank Kabel" wrote:

Hi
you may try using a helper column with the formula
=TEXT(A1,"[hh]:mm")


--
Regards
Frank Kabel
Frankfurt, Germany
"Jack Cutrone" <Jack schrieb im
Newsbeitrag ...
I am trying to import an excel spreadsheet with 12000+ records to access
but
can't stop a formatting problem for a time duration field. The data
appears
correctly in Excel cells as E.g. 00:17:33 but in the formula bar as
"12:17:33
AM" and the latter is what ends up in the Access database. I have
unlocked
the cell format protection, have reformatted the column using cell
format
to
[hh]:mm:ss or [mm]:ss and the format changes will appear in the data in
the
column but still appears in the formula bar as 12:17:33 AM. It is
apparent
that Excel is interpreting the entry as a time of day rather than a
duration.
How can I stop this? (As I mentioned there are over 12,000 records in
the
spreadsheet so PLEASE don't suggest anything that requires reentering
all
the
data :).]

not sure how to do this but my database is quite small so i did get it
handled by editing, thanks

my next qustions is eliminating the old data, or at least hiding it, what's
the best way to do this?




"Frank Kabel" wrote:

Hi
if your date values are in column A, enter this formula in B1 and copy down
for all rows. Now import column B in your database

--
Regards
Frank Kabel
Frankfurt, Germany
"hoeker" schrieb im Newsbeitrag
...
i am fighting the same issue and appreciate your continued help.

i do not know how to implement this solution, could you please expand your
directions?

thanks.
ross hoek

"Frank Kabel" wrote:

Hi
you may try using a helper column with the formula
=TEXT(A1,"[hh]:mm")


--
Regards
Frank Kabel
Frankfurt, Germany
"Jack Cutrone" <Jack schrieb im
Newsbeitrag ...
I am trying to import an excel spreadsheet with 12000+ records to access
but
can't stop a formatting problem for a time duration field. The data
appears
correctly in Excel cells as E.g. 00:17:33 but in the formula bar as
"12:17:33
AM" and the latter is what ends up in the Access database. I have
unlocked
the cell format protection, have reformatted the column using cell
format
to
[hh]:mm:ss or [mm]:ss and the format changes will appear in the data in
the
column but still appears in the formula bar as 12:17:33 AM. It is
apparent
that Excel is interpreting the entry as a time of day rather than a
duration.
How can I stop this? (As I mentioned there are over 12,000 records in
the
spreadsheet so PLEASE don't suggest anything that requires reentering
all
the
data :).]