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

[Guest]

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 .]

 

[Frank Kabel]

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

 

[Guest]

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

 

[onedaywhen]

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.

--

 

[Guest]

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

 

[Guest]

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

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


--
Regards
Frank Kabel
Frankfurt, Germany

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 .]

 

[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

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

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


--
Regards
Frank Kabel
Frankfurt, Germany

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 .]

 

[Guest]

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?

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

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

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 .]