Help with coin-collecting sheet

[JenniferMurphy]

My 9-year-old grandson discovered that quarters (in the US) come in a lot of styles that commemorate states, national parks, and women. He now wants to start a collection and see how many he can get.

To help him, I started to create a checklist sheet so he could see which ones he has and which ones he needs to get. I found this page on the the US Mint website:

Coin and Medal Programs | U.S. Mint

It has links to details about the various programs. This page is about the America the Beautiful program:

America the Beautiful Quarters® Program | U.S. Mint

I started to copy these tables into a sheet, but ran into a problem with Excel's inability to handle dates before 1900. The minisheet below contains the second table from the US Mint page. I just copied and pasted. The first 4 "dates" in the Date column, all before 1900, came in formatted as General. If I try to sort on any of those, it's a text sort. The last one, 7/01/1902, came in as Date. If I try to sort on that one cell, it says it's a Date sort, but it looks like the other 4 are sorted as text.

All of the dates in the Release Date column came in as Date. I then added the As Date columns formatted as "m/dd/yyyy_0". They don't work properly for any of the "dates".

I have two questions:

1. Is there any way I can get all of these dates, including those before 1900, to all behave as dates both for display and for sorting?
2. Why does the the "m/dd/yyyy" portion of the custom format work, but the "_0" part does not?

Thanks

Quarter Coin Checklist.xlsx
	C	D	E	F	G	H
4	Jurisdiction	Site	Date	As Date1	Release Date	As Date2
5	Pennsylvania	Gettysburg National Military Park	2/11/1895	2/11/1895	01/24/11	1/24/2011
6	Montana	Glacier National Park	2/22/1897	2/22/1897	04/04/11	4/04/2011
7	Washington	Olympic National Park	2/22/1897	2/22/1897	06/13/11	6/13/2011
8	Mississippi	Vicksburg National Military Park	2/21/1899	2/21/1899	08/29/11	8/29/2011
9	Oklahoma	Chickasaw National Recreation Area	07/01/02	7/01/1902	11/14/11	11/14/2011
Sheet2

Cell Formulas
Range	Formula
F5:F9	F5	=[@Date]
H5:H9	H5	=[@[Release Date]]

 

[felixstraube]

1. Not that i know of.*
EDIT. Sorry it does not sort as it should... now sure why.

 

[JenniferMurphy]

1. Not that i know of. Only thing that ocurres to me is write the dates as text in this format "yyyy/mm/dd" (with leading zeros for month and days). When you sort it, it will as expected.

Book2.xlsx
	A	B	C
1	Jurisdiction	Site	Date
2	Pennsylvania	Gettysburg National Military Park	1895/02/11
3	Montana	Glacier National Park	1897/02/11
4	Washington	Olympic National Park	1897/02/22
5	Mississippi	Vicksburg National Military Park	1899/02/21
6	Oklahoma	Chickasaw National Recreation Area	2002/07/01
Sheet3

I was thinking of something like that. Any suggestions for an expression that will convert the downloaded data into that format?

 

[felixstraube]

Ok at least in my version and with my regional configuration when i format it like this:

It works correctly. Don't know why with the slash it wont work. I guess that it recognizes it as date but then it has problems with the dates that are before 1900.

Column E is the downloaded data? Is it formatted as text?