date format problem

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
 
N

Norman Harker

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

Mike

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

Norman Harker

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

Mike

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

Norman Harker

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

Norman Harker

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.
 

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

Similar Threads

Problem formatting dates from CSV file 2
Conditional Formatting - Dates 2
Why this paste result? 4
Converting date format 4
Weekend dates conditional format 3
date format problem 1
Formating Numbers 11
Date Format Glitch 5

Top