Converting Serial dates to MM DD YYYY - anthonyblackham/GIS-Wiki GitHub Wiki
History Lesson
Since the dawn of time well... before whenever now is, people have had their own opinions on what time is and how they quantify it. The vast majority of society's perception of time has revolved (pun intended) in some form around the revolutions of the Earth, Stars, Planets (and dwarf planets), moons, and the Sun which is technically a star but who is keeping track of all this anyways?
The last few centuries ish there were really two types of calendars:
- Gregorian Calendar (Tropical year is ~365.242189 days)
- There is a leap year every year whose number is perfectly divisible by four - except for years which are both divisible by 100 and not divisible by 400, or in simpler terms, every 4 years except for every 100 except for 400, eg 1900 was not a leap year, 2000 was. 2100, 2200, 2300, are not, 2400 is. etc.
- Julian Calendar (Tropical year is 365.25 days)
- Leap year every 4 years
We use a Gregorian Calendar these days as its "more accurate" at least moreso than the Julian Calendar.
Some Calculations
Julian Calendar:
Calendar Year: 365
Julian Tropical Year: 365.25
Leap Year: Every 4 years
Assuming we start at 0 AD and go 2000 years:
Calendar Year: 2000 * 365 = 730000
Tropical Year: 2000 * 365.25 = 730500
Over 2000 years we've gained 500 days.
Taking into account leap years:
2000/4 = 500 Leap years
So if you subtract 500 Leap Days from the 500 Days extra it works out great except for the fact that the real tropical year for the earth is ~365.242189 which means after 2000 years you are 484.38 ahead which means that 500-484.38 = 15.62
So you're really 15.62 days ahead after 2000 years or 374.88 Hours or 22492.8 Minutes or 1349568 Seconds. Which means that you're really gaining 674.784 seconds a year or 1.85 seconds a day
Now lets compare the Gregorian Calendar:
Gregorian Calendar:
Calendar Year: 365
Earth's Tropical Year: ~365.242189
Leap Years: Every 4 years except centuries not divisible by 4
Assuming we start at 0 AD and go 2000 years:
Calendar Year: 2000 * 365 = 730000
Tropical Year: 2000 * 365.242189 = 730484.3781
Over 2000 years we've gained ~484.38 days which means we'd be a year and a 4 months ahead. We'd be having Summer weather during Christmas (depending on where you live on the earth as well)
Taking into account leap years:
2000/4 = 500 Leap years
Centuries Divisible by 400: 2000/400 = 5
Centuries: 2000/100 = 20
Skipped Centuries: 20-5 = 15
500 - 15 = 485 days
So based on the tropical year we gain 484.38 days and when we counter in leap years we skip 485 days. so 485-484.38 = .62 which means after 2000 years we are .62 days ahead or 14.88 hours or 892.8 minutes or 53568 seconds which means that on average we are only gaining .073 seconds per day. You can also factor in earth slowing its rotation and other astronomical and geological factors but generally speaking that's not terrible odds. There are even more detailed models that incorporate leap seconds but most people aren't going to live long enough to see the .073 seconds add up to anything significant so I doubt most people care enough to change the Gregorian Calendar model.
Granted as complicated as this may seems its actually a vast oversimplification by omitting political details. If you really want to understand the history and politics behind modern society's use of calendars the Perpetual Calendar is a good place to start.
The history of time is important to understand so we can understand what is happening with the dates in different packages of software:
Software Nuances
Excel
The underlying code in excel uses a serial number to define dates and if your cells aren't formatted as a date like mm/dd/yyyy then you'll likely see the serial date number.
The serial number is a form of "Julian Date" which in the programming world has become a bit of a misnomer as now most calendars are based on the Gregorian Calendar rather than the Julian Calendar. To simplify our definition of a Julian Date or Julian Day, it is basically a serial number of the days from the beginning of a year. To eliminate ambiguity with the terminology of Julian dates not actually being based on the Julian Calendar, it makes more sense just to call them "ordinal dates" or "day of year." Astronomers have used a Julian date which is a count of days since noon January 1, 4713 BC which actually is based on the Julian Calendar. Excel on the other hand starts their time at January 1, 1900.
The problem is that they assume 1900 was a leap year but as discussed earlier the Gregorian Calendar doesn't have leap years on centuries not divisible by 4. so depending on how you run your conversions if you have dates between 1/1/1900 and 3/1/1900 you may end up being off by a day which means you'll need something like if x <= 60 then serialdate + 1 to get it to convert correctly.
For example: 39448 is the serial number of the date 1/1/2008 or in other words 1/1/2008 is 39448 days since 1/1/1900 (including 2/29/1900)
See the following links for more info. but in short they historically kept the bug to be compatible with another software and now it's just too late.
https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year https://support.office.com/en-us/article/DATEVALUE-function-df8b07d4-7761-4a93-bc33-b7471bbff252
Excel on the mac has its starting point at 1/1/1904 to skip the whole leap year issue.
Convert Serial Date Field Calculator
Convert int to long with python
int (!field!)
Field Calculate:
datetime.datetime(1899, 12, 30) + datetime.timedelta(days= !StartDate! )
Other notes: Sidereal year = 365.014172 (which calendar uses sidereal years?)