date format problem

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a csv file with date column in the "01/05/1990" format. I could use
Notepad and open the file with the correct date displayed ok.
When I double click the file and Excel opens it, the formats becomes very
strange:
some are right justified with "-" as delimiter e.g. "01-05-90"
some are left justified with "/" as delimiter e.g. "01/19/1990"
I tried to fomat the column to a common date format like "01-Mar-1990" etc
but Excel refuses to change.
Any help?
Thanks.
Mike
 
Hi Mike!

First check those / dates. I suspect that they are text. Use
=ISTEXT(A1)

Next check whether the date 01-05-90 is the date you think it should
be using a format dd-mmm-yyyy

Then report back giving details of your Regional Settings date form
(e.g. mm-dd-yy)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
1. Istext(a1): some are false (A1,A2) "01-05-90" ...Some are true (A3, A4)
"01/19/1990" etc
2. Yes. The dates are correct - by visual inspection, not using Excel's
date calculation. See (1).
3. Regional setting in "Windows Control Panel":
locale: English (Australia)
Short date format: dd-MMM-yy
Date separator "-"
Long date format dddd, MMMM dd, yyyy

From these it seems that the problem is when opening the .csv file, Excel
interprete some dates as text, some as dates, although their
original format is the same. Any ideas?

Thanks.
 
Hi Mike!

My suspicion is that the source has dates that are in US format
mm/dd/yy This means that where dd is 12 or less the dates are being
interpreted as dates by your settings but the these dates are wrong!!!

e.g. Source date of 05 Jun 90 is in source as 06/05/90 but when it
comes over is interpreted as 06-05-90 by your settings which is
06-May-90

But a date 31 Jun 90 is in source as 06/31/90 but when it comes over
is not accepted as a date because month 31 doesn't exist. It therefore
gets treated as text.

One solution is to:

Scrap your existing dates
Change your Regional settings to English (US) [I know it's an
oxymoron!]
Import your csv file data again
Change your Regional settings back to English (Dinky Di)

There are other solutions but I think that's the easiest. The
alternative is to set up a helper column and use a formula on all the
dates:

=IF(ISTEXT(A1),DATE(IF(--RIGHT(A1,2)<30,--RIGHT(A1,2)+2000,--RIGHT(A1,
2)+1900),LEFT(A1,2),MID(A1,4,2)),DATE(YEAR(A1),DAY(A1),MONTH(A1)))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Bingo. That's the source of the problem. Everything's ok now.
Thanks.

Norman Harker said:
Hi Mike!

My suspicion is that the source has dates that are in US format
mm/dd/yy This means that where dd is 12 or less the dates are being
interpreted as dates by your settings but the these dates are wrong!!!

e.g. Source date of 05 Jun 90 is in source as 06/05/90 but when it
comes over is interpreted as 06-05-90 by your settings which is
06-May-90

But a date 31 Jun 90 is in source as 06/31/90 but when it comes over
is not accepted as a date because month 31 doesn't exist. It therefore
gets treated as text.

One solution is to:

Scrap your existing dates
Change your Regional settings to English (US) [I know it's an
oxymoron!]
Import your csv file data again
Change your Regional settings back to English (Dinky Di)

There are other solutions but I think that's the easiest. The
alternative is to set up a helper column and use a formula on all the
dates:

=IF(ISTEXT(A1),DATE(IF(--RIGHT(A1,2)<30,--RIGHT(A1,2)+2000,--RIGHT(A1,
2)+1900),LEFT(A1,2),MID(A1,4,2)),DATE(YEAR(A1),DAY(A1),MONTH(A1)))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Mike said:
1. Istext(a1): some are false (A1,A2) "01-05-90" ...Some are true (A3, A4)
"01/19/1990" etc
2. Yes. The dates are correct - by visual inspection, not using Excel's
date calculation. See (1).
3. Regional setting in "Windows Control Panel":
locale: English (Australia)
Short date format: dd-MMM-yy
Date separator "-"
Long date format dddd, MMMM dd, yyyy

From these it seems that the problem is when opening the .csv file, Excel
interprete some dates as text, some as dates, although their
original format is the same. Any ideas?

Thanks.
Click to expand...
Click to expand...
 
Hi Mike!

Good to hear it's sorted.

Out of curiosity, did you use the change Regional settings approach or
the formula?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Mike!

Re: "I have permanently changed the regional setting to English(US)."

I hope you get well soon. <vbg>
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top