Change Date format

[David]

Hello,

I am not even sure if my Excel spreadsheet sees the column as a date. I have
genealogical information that I copied/pasted directly into an excel
spreadsheet.

The column with the dates show as dd/mm/yyy instead of mm/dd/yyyy.
- I am lookin gfor the easiest way to make it a recognizable date column
instead of retyping 18/01/1765 over to 01/18/1765, etc. I have over 450
listing and any help to make Excel recognize it as a date and then reformat
it into a useable date to sort with would be helpful.

Thank You.
David

 

[Niek Otten]

Hi Davis,

See

http://www.j-walk.com/ss/excel/files/xdate.htm


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
| I am not even sure if my Excel spreadsheet sees the column as a date. I have
| genealogical information that I copied/pasted directly into an excel
| spreadsheet.
|
| The column with the dates show as dd/mm/yyy instead of mm/dd/yyyy.
| - I am lookin gfor the easiest way to make it a recognizable date column
| instead of retyping 18/01/1765 over to 01/18/1765, etc. I have over 450
| listing and any help to make Excel recognize it as a date and then reformat
| it into a useable date to sort with would be helpful.
|
| Thank You.
| David

 

[David]

Thank You Vergel,

It's true that Excel can not tell dates before 1900 so I found my work
around doing a search in the General questions:

Subject: Re: Formatting dates prior to 1900 3/30/2005 1:42 PM PST
By: Fredrik Wahlgren In: microsoft.public.excel.misc

Yes. Let's say you have entered '14/02/1834 in A1 and downwards. Copy the
column and paste the values to column B.
Now, select column B and use Data|Text to columns. The wizard is easy to
understand. The dates will be split to thre columns.
Select columns A to D and select Data|Sort. Sort first on D, then on C and
then on B. When you're done, you an delete columns B to D.

/Fredrik

Instead of deleting the columns after I just hid them in case I or someone
else needed it afterwards.

David

 

[Vergel Adriano]

I believe the earliest date value that Excel would recognize as a date value
is 1/1/1900. Assuming the text date in A1 is in dd/mm/yyyy format, to
convert it to a date value in B1, you can use this formula:

=DATE(RIGHT(A1,4),MID(A1,4,2), LEFT(A1,2))