Dates Are Difficult
Dates Are Difficult
Written by Mike James   
Wednesday, 21 August 2013
Article Index
Dates Are Difficult
Hot Dates
Date Bugs


Hot Dates

So far so good. Time is fairly easy as long as you are working with intervals of a few days. Things really only start to get complicated when you start to work with dates specified in day, month and year.

The reason is that at this level there is very little regularity in the way time is measured and it is the definition of the month that makes things complicated. The month is not a fixed period of time. If I say that I will finish a project in a month - what exactly do I mean?

There are 7 days to a week and that’s about as simple and regular as it gets. A year is 365 days, which is the time it takes the earth to go around the sun – to be more precise it actually takes 365 days, 5 hr, 48 min, and 45.5 sec on average to go round.

Because the orbital period of the earth isn’t an exact number of rotations about its axis we have to tinker with the number of days in a year to correct the calendar every so often – hence leap years which have 366 days. As the error reaches 0.97 of a day after four years we add an extra day in February and call it a leap year.

As this too still leaves a little bit of error we have to skip leap years every century or so. This results in the rule that every year divisible by 4 is a leap year except that century years, e.g. 2100, are not leap years - unless they are divisible by 400.

So even the unit of one year isn't fixed but in general use if you say that something will take a year then you mean 365 days.  


Lunar and solar

Leap years are a minor irritation when compared to the problem of the month. The month was originally the time it took the moon to go from full moon to full moon i.e. the time for the moon to go around the earth once.

The lunar or synodic month is a fairly inconvenient 29.53059 days on average. Usually we quote a lunar month as 29.5 days but the half a day still makes it a totally unworkable unit of time. If you take 12 lunar months then you get a year with only 354 days and this is 11 days shorter than a solar year.

There are many different ways of making the lunar year and the solar year agree to a greater or lesser extent and this is the origin of the different calendars that are in use.

For example the Islamic calendar has 12 lunar months but uses leap years to correct it. The Jewish calendar uses alternating 29 and 30-day lunar months. To correct it an extra month is added every 3 years. Interestingly the old Egyptian calendar is very similar to our own in being based on a solar year but it used 12 lunar months of 30 days but with 5 extra days tacked on at the end of the year. They even managed to correct over a longer period by adding a day every four years, i.e. they invented the leap year.

The calendar almost universally in use today is a slightly modified version of the Julian calendar, invented over a fairly lengthy period of time, by the Romans and, of course named after Julius Caesar. This more or less gives up on the lunar month but still divides the year into 12 calendar months with lengths of 28 to 31 days.

This is where things get really irritating for a computer because this isn’t just arithmetic to a different base – the base changes with the month!

So if you are counting days you have to test to see which month you are in to know when to move to the next month. There is no way that you can simply convert 6 months say to an equivalent number of days because it all depends which months you have selected.

This is all very complicated and we didn't get it right at the first try.

The Julian calendar was initially wrong by roughly 11 minutes per year and this error accumulated until in 1582 it became noticeable as a 10-day discrepancy. To correct this problem 10 days were dropped from the year with much complaining about stolen time.

To stop the problem happening again the remedy of skipping leap years at the turn of the century (unless divisible by 400) was added. This is the Gregorian calendar and it is what we use today. The Soviet Union only adopted the calendar in 1918 and Greece only in 1923. It is important to know these things because if you plan to interpret dates from different countries and from before the universal adoption of the Gregorian calendar then you need to be aware that there could be a 10 day discrepancy! There is also the small matter that many countries continued to use older or religious calendars long after the reform.

Working with very old dates is doubly difficult. 

Financial Calculations

You may have encountered the idea of the "financial year" or 360 day year and wondered why.

Before the days of the computer, finding the number of days between any two given dates was a difficult problem. To make things easier the financial world used a calendar that assumed that every month had 30 days, making the financial year 360 days.

So if you borrowed money for two months the idea was that you would pay 60 days interest no matter which months were involved. Of course how to actually do this calculation is something that has to be agreed by all parties before the transaction and there are different ways of doing it. You can still compute date differences using the 360-day financial year in most spreadsheets and Excel, for example, will allow you to specify that either the US or the European method.

There are lots of other examples of simplifications of calculation in the financial world that were introduced to make the sums easier. You generally don't need them today but you will still encounter them and if you have to deal with old transactions you may have to work with them. 

Date Serial Numbers

However, today there is little reason to use a financial year as date/time data types generally store the data as the number of days from some fixed point and this means we can work out the number of days between any two dates exactly. 

The way that this works is that dates and times are stored as the time interval from a given date - either in days and fractional days or as the number of seconds or some other unit. 

That is when you enter a date in day, month, year format it is automatically converted to the number of days or time from a fixed date – this is often called a date serial number.

Excel, for example, uses 1 January 1900 as day 1 but it also supports an alternative system that takes 2 January 1904 as day 1 to remain compatible with older versions and the Mac version of Excel.

Different systems use dates to calculate the interval from. For example, the Unix time and date system works by simply counting the seconds since the 1st of January 1970 - which is known as the Unix Epoch.

So using this simple idea we have a completely foolproof way of handing dates and times and there is no need for the financial year or any simplifications.

If you want to work out the difference between two dates simply convert them to date/time serial numbers and subtract. For example in Excel you would use


to work out the number of day between the two dates. You can add a number of days to a date in the obvious way:


adds 10 days to the date and this works even if it rolls over a month or year boundary.

Things are more difficult if you want to add a month or even a year to a given date because these units don’t correspond to a fixed number of days.

If your intention is to increment the month number by one rather than move on 30 days say then you generally have to resort to complicated methods that convert the date to day,month,year format, add one to the month and convert back to date serial number.

Some languages, Visual Basic for example, do provide functions that will add a month or a year to the date specified (lookup the DateAdd and DateDiff functions).





Last Updated ( Wednesday, 21 August 2013 )

RSS feed of all content
I Programmer - full contents
Copyright © 2018 All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.