default date/time format

P

Pete

Hi,

is there a way to change the default date/time format? I'm
often opening CSV files with date_time fields in the
following format:
dd/mm/yyyy hh:mm:ss.00
e.g.
17/10/2002 13:55:49.82

but Excell automatically detects this is a date/time, and
dislays it with the format:
mm:ss.0
e.g.
55:49.8

I currently have to select the date/time cells and change
the cell format to the custom one above.

can I change the defaults so that this is the format used
automatically?

many thanks

Pete
 
J

Jim Rech

Curiously it's the presence of the tenths/hundredths of seconds that causes
this. Without them (e.g. 17/10/2002 13:55:49) I get the expected date
format. The same thing happens when the entry is made manually so it's all
part of Excel's "helpful" date recognition feature. I really don't think
there is anything you can do about this. You could of course use a macro
that does the file opening and date formatting in one step.

--
Jim Rech
Excel MVP
| Hi,
|
| is there a way to change the default date/time format? I'm
| often opening CSV files with date_time fields in the
| following format:
| dd/mm/yyyy hh:mm:ss.00
| e.g.
| 17/10/2002 13:55:49.82
|
| but Excell automatically detects this is a date/time, and
| dislays it with the format:
| mm:ss.0
| e.g.
| 55:49.8
|
| I currently have to select the date/time cells and change
| the cell format to the custom one above.
|
| can I change the defaults so that this is the format used
| automatically?
|
| many thanks
|
| Pete
 
Top