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

J

Jack Cutrone

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 :).]
 
P

Peo Sjoblom

Assuming you have windows, go to start>control panel>regional options/settings
change the time format to HH:mm:ss (24 hour) as opposed to hh:mm:ss (12 hour)

Regards,

Peo Sjoblom
 
J

Jack Cutrone

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
 
H

hoeker

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 said:
Hi
you may try using a helper column with the formula
=TEXT(A1,"[hh]:mm")


--
Regards
Frank Kabel
Frankfurt, Germany
Jack Cutrone said:
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 :).]
 
F

Frank Kabel

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 said:
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 said:
Hi
you may try using a helper column with the formula
=TEXT(A1,"[hh]:mm")


--
Regards
Frank Kabel
Frankfurt, Germany
Jack Cutrone said:
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 :).]
 
H

hoeker

Now import column B in your database

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 said:
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 said:
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 said:
Hi
you may try using a helper column with the formula
=TEXT(A1,"[hh]:mm")


--
Regards
Frank Kabel
Frankfurt, Germany
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 :).]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top