Bluepoint Design

Dates and their rules are complex and most computer applications essentially convert date and time to an integer and decimal values starting from a specific point in time. The Julian date system for instance has a value of one for January first 4713BC. So each day has an value of one and fractions of a day are decimal. But applications like MEP, Lotus 1-2-3, and most MS Office applications use a Serial Date system that ideally would/should have a value of 1 that corresponds with 1/1/1900. More on that in a bit. So that means that today, right now as I'm writing is a decimal value of 39837.74236. This way if we want to move ahead one week we can just add 7 or if we want to find something like the average birthday for a group we can. Also values like the difference between two dates are stored in this fashion. So I might Subtract one date from another and end up with chunks of time like 3.5 days. I mention this because the time variable manipulations commands in MEP are very limited so if one wants to do much more with them you must convert to a decimal variable where one can do more operations. So far life is good and simple. Now if you venture out of the standard MEP time manipulation commands you need to know a few things about zero in this system, especially if you want to interact with MS Office applications like Excel.

Now the first thing you need to be aware of a conceptual problem with time. Have you ever noticed how we regard this as the 21st century when out numbers for the year starts with 20? That's because the first century started at year 1, not 1000. The problem is that base ten systems have a zero and there are no zeros in dates. So if we are on the first day of the year it might be represented with a 1 but the actual time value at noon is 0.5 days. For this reason in my time chart below I offset the serial value numerals so they occur at the finish/start of the day. The important thing here is that 1/1/1900=1. Now people are often confused by this because you would think this is the beginning of this time period is 12:00AM on 1/1/1900 but you would be wrong. This time period begins, or it's Zero value, is actually on 12/31! There reasoning for this becomes more apparent if you consider how you would represent noon on 1/1/1900. In this system it's 1.5 so the 1 corresponds to the one but in order t do this zero has to be pushed back to 12/31. See the first two lines in my illustration below for a visual.

The next problem has to do with a goof by the programmers who created Lotus 1-2-3 and their lack of understanding concerning leap years. Now we all know that we insert an additional 'leap day' at the end of February every year that's evenly divisible by 4 but there's more to it than that. The rule actually is that the leap day is added if divisible by 4 and not divisible by 100 unless divisible by 400. Since 1900 is divisible by 4 but also divisible by 100 and not 400 then there is no leap day. But they missed that so in Lotus 1-2-3 you can type in 2/29/1900 and it's a valid day even though it never existed! Now when Excel was young it had to compete with Lotus 1-2-3 and they initially had a proper date system but it differed by one to Lotus. To make them cross compatible MS decided to intentionally flaw Excel to be compatible! Now you may think, so what but believe it or not there are times when it comes up. For instance calculating the day of the week. By the bye Excel's intentional flaw allows a leap day in 1900 but is inconsistent because it does not allow leap days in equivalent years like 2100. EG 73109 is 2/28/2100 and 73110 is 3/1/2100!

Now where this comes into play is because MEP is written in Delphi which uses a very similar date system with a tweak. For extreme dates a programming language needs to follow all the rules regarding leap year so it's my belief they came up with a compromise to avoid the conundrum MS faced. Their solution was to simply offset their zero date by one. This mean from 3/1/1900 on the serial values are the same as MS apps but doesn't have the illegal date of 2/29/1900 and can apply the proper leap day rules thru eternity. So combined with the conceptual time problem I mentioned before this means zero for the Delphi system is all the way back on 12/30/1900. To illustrate I created this graphic that shows the days near zero and the first leap day that I think makes this all clear.

So once you understand how time begins for these systems there is almost no limit to what you can do with a little bit of logic in MEP. The power here is you can always convert form decimal variable types to time which makes it easy to get a text date value. If you have any questions or comments on this article please drop me a line on my contact page. I like debating time concepts like why noon is not 12:00PM so I would be happy to hear from you.  Also I love writing time manipulation scripts so if you have any needs I'm available for hire.

Created: 2009/01/24 1733