Got dates in dd/mm/yy, Excel refuses to understand this

D

Daniel Klein

So I've got an excel table from an external source that has dates in
it in the dd/mm/yy format. I'm trying to make Excel understand this,
but it won't. I've tried changing the
 
R

Ron Rosenfeld

So I've got an excel table from an external source that has dates in
it in the dd/mm/yy format. I'm trying to make Excel understand this,
but it won't. I've tried changing the
Click to expand...

There are likely several things going on. If NONE of the dates are understood by Excel, then:

Although the dates may appear to be in the correct format, they are really text strings.
If one of your misbehaving dates is in A1, and you enter a formula =istext(a1) it may return TRUE.

Another possibility is that your Windows Regional Settings are set to a different order, in which case SOME of the dates might be recognized, but incorrectly.

If NONE of the dates are being recognized, try this:

Select the misbehaving dates
Data / Text to Columns
<Next>
<Next>
Select Date and DMY
<Finish>

If that doesn't work, then there are likely some non-printing characters in the cell(s) that we will have to clean up.
 

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

How to enter a date in DD-MM-YYYY format 2
VBA - exporting serial letters to single documents 0
How do I change dd/mm/yy to dd-month-yy? 6
Forcing a date entry format of dd/mm/yyyy 1
change DD/MM/YY to MM/DD/YY 6
Date Format 10
Dumb question alert! What program to use? 0
Macro that shows range of dates 6

Top