Date to Text (please read)

[Guest]

Yes, I know this has been covered a MILLION times yet although I've looked
through many threads, I can't seem to find one that covers this EXACTLY.
I'm importing info into Excel 2000. One of the columns is a date field..
but it is not consistent.. for example, it can read 10206 or 100206 (both to
mean Oct 2, 2006). When I try to format it into a date (10/2/06) it turns it
into something strange, such as 01/02/36. Don't know why this is happening.
When I click on a seperate cell w/out any info (that is formatted to a date
field), and type in 100206 it STILL doesn't come out right! HELP!!! I need
this info for tomorrow!!!

 

[Guest]

By now, I'm sure you're aware that you have a complicated data problem.
If 10206 is supposed to be 10/02/2006
Then what value would be used to mean 01/02/2006? Wouldn't it also be 10206?
Also....is 11206 11/02/2006? or is it 01/12/2006?

Apart from that...if you can get the values to all be in this form: mmddyy
then you could use <data><text-to-columns> and designate the data as Dates
in mdy format.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

If both 100206 and 10206 are 10/2/2006 then what is 1/2/2006 ??

To make a formula we need an un-ambiguous definition of the field digits.

 

[Alan]

Excel treats dates as the number of days elapsed since the first of January
1900, for example today, the 15th of November 2006 is actually 39036. You
can format it in many different ways, but the real content of the cell is
still 39036.
When you enter a date into a cell like today's date it has to be entered as
15/11/2006 or 16-11-2006 to get Excel to recognise that you are entering a
date, it then converts it to the number which can be re-formatted.
What you are importing, eg 10206 will not be recognised as a date, just as a
number. If you format it as a date it will give the date as the 10206th day
after 01-01-1900.
There are ways of using text formulas followed by paste special > values to
convert a number to the date value you need, but I'm afraid that if you're
importing stuff that has two or more different ways of representing the
date, its going to be very time consuming to achieve this.
Sorry to be the bearer of bad news,
Regards,
Alan.

 

[Guest]

The reason you have not found a satisfactory answer is that there is no good
answer for this kind of data... For example how should we handle 11106... Jan
11 or Nov 1. There is no way to interpret that without knowing the intent of
the person who entered the data in the first place.

 

[David Biddulph]

Try =DATE(2000+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,LEN(A1)-4)) with the data
you've got.

The reason you don't get the right answer when you type in 100206 is that
you should be typing 10/02/06