bangus;3835477 said:
Use the Excel sort feature to sort the birthdate column so its youngest to oldest. Choose a birthday value and copy and paste it to the first 2000 players. Choose another birthday value and apply it to the next 2000 players so that they're 1 year older. Etc, and so forth. The only tricky part is you have to experiment a bit to figure out which values = which birthday year, because they're written in a 6-digit code.
I think I can help clarify this a little further. Excel, SQL, and EA all store dates as an integer which counts from a specific date. Consider Matt McKay (Australian International).
McKay's birthdate is 01/11/1983 (mm/dd/yyyy). Excel starts counting at 1 from 1/1/1990. You'll sometimes notice that if you try to treat dates before 1900 like dates in excel, it can sometimes act a bit funny. There are many reasons for this, but the fact that it goes to 0 and then negative.
You can prove excel's treatment by simply setting three consecutive cells to 1
0
-1
Then highlight those cells and format them as dates.
The first will show 1/1/1900
The second will show 1/0/1900 (the 0th of January?)
The last one just won't process (I think it just shows a set of pound signs).
SQL alternatively starts counting at 0. It can handle negative values. And it considers the value entered to be a datetime.
If you enter the following code in a new sql script:
select convert(datetime,1)
select convert(datetime,0)
select convert(datetime,-1)
It will return 2nd of January 1900 at 00:00:00.000
1st of January 1900 at 00:00:00.000
31st of December 1899 at 00:00:00.000
Any date in either program is just stored as an integer that counts the number of days from their starting point.
EA does exactly the same thing but they add a constant to each birthdate.
For excel the constant is 115859
it's one off for SQL because off the difference in starting positions. In SQL it is just 115860.
So if you want to convert EA birthdates to excel dates, just subtract 115896 from the ea date. If you want to convert it back, just add that same constant to a birthdate stored normally in excel.
If you want to do the same in sql, just add an additional day.
Bangus is right that you can easily do what you're trying to in excel. Just subtract 1826 (I think) from all players dates in EA. 365*5 +1 (I think we've had only one leap year? Right?)