Calendrical Calculations, Part 1: Cyclical vs. Linear

— Arvada, Colorado UNITED STATES

By some accident of probability, I have exactly eight nieces and no nephews. The fact that they're all nieces isn't relevant to the story; it's just interesting. The fact that there are eight of them is relevant.

So, it's my job as the bachelor uncle to buy the cool toys for my nieces on their birthdays. Since there are eight of them, I'm going to need help remembering their birthdates. Yes, I know that they have calendars and apps for your phone that will tell you these sorts of things, but I want us to understand how those apps work.

First, I e-mail my sister in Mexico, Helena, and my sister-in-law across town, Susan, to get a list of all the birthdates in their families. I'm going to compile their responses into a table of some sort.

DanielMarch 4, 1986
LanceOctober 6, 1955
LucyApril 14, 1956

Yuck. The dates don't look the same. Helena lives in Mexico, so, as one would expect, she provided the dates with the day of the month first, the month next and the year last. Susan, on the other hand living in the United States, gave me the dates in the American format: the month, followed by the day of the month and then followed by the year. Helena used four-digit years while Susan gave me two-digit years. I myself entered in a few birthdates for family members that I already knew, but I spelled out the months. When I got the responses, I just threw them onto a table the same way that I got them. They need to be consistent.

Let's try putting them all in my format.

AmyOctober 15, 2010
AnaSeptember 23, 2007
AnastasiaMarch 30, 2013
CatherineSeptember 17, 2012
DanielMarch 4, 1986
ElisaMay 31, 2015
EmilyApril 2, 2014
HelenaOctober 3, 1982
JulianaMay 6, 2002
LanceOctober 6, 1955
LucyApril 14, 1956
NicholasJune 22, 1984
PatriciaMay 28, 2015
SamuelMay 22, 1981
SusanJanuary 22, 1985

Now, it's sorted alphabetically by name. Here's the thing: I need to know who's birthday is coming up next. If the list were in order by birthday, I may be able to figure this out better. Let's tell the computer to sort it that way.

LucyApril 14, 1956
EmilyApril 2, 2014
SusanJanuary 22, 1985
NicholasJune 22, 1984
AnastasiaMarch 30, 2013
DanielMarch 4, 1986
SamuelMay 22, 1981
PatriciaMay 28, 2015
ElisaMay 31, 2015
JulianaMay 6, 2002
AmyOctober 15, 2010
HelenaOctober 3, 1982
LanceOctober 6, 1955
CatherineSeptember 17, 2012
AnaSeptember 23, 2007

That didn't turn out the way that I expected it. It sorted it alphabetically by month. Also, it didn't even sort properly within the month. Look at the month of May: Samuel, Patricia and Elisa's birthdays seem to fall in order. However, Juliana's birthday is before any of theirs, but she's listed after. That's because ‘6’ comes after ‘2’ and ‘3’ when we sort alphabetically. We need to make these numbers somehow.

At my work, when we enter in dates into the computer system, we enter a two-digit month, a two-digit day of the month and then a two-digit year. For example, today, 2018 September 16 would be entered in as “091618”. Let's try entering the dates with this method and then sorting on that.


Yes, this really is how we enter the dates into the computer at work, and it's a bad way to do it. At least now, they're in the order that I want. Also, Catherine's having a birthday tomorrow! I need to get her a gift tonight! Before I can do that, I need to know how old she's going to be. Of course, I could just look at the number and deduce that she'll be six, but I need to get the computer to figure that out. Actually, what will be helpful would be to sort the list in such a way where the oldest people are on top and the youngest on the bottom. To do that, I need to change the date format again.


Okay, we have a four-digit year, two-digit month and two-digit day of the month. This is still a bad way to do it in my opinion. However, there are computer systems that use this method. MySQL uses a similar method for storing its dates. Why is this bad then?

Let's take a moment to talk about time in the platonic sense. Not only do we perceive time in cycles, we order our lives around these cycles as well. Think of how often you wake up, attend religious ceremonies, get paid, pay the rent or the mortgage, pay taxes, vote or watch the Olympics. All of these events occur in cycles. The day is probably the most fundamental of these cycles, but even the day is broken up into smaller cycles. An analogue clock is the absolute perfect way to represent this. The instant that the second hand reaches the top of the clock, a new cycle of sixty seconds begins, and the number of these cycles that have passed is represented by the minute hand (and, similarly, the hour hand). Since we perceive time in cycles, it is natural that we represent time that way in our speech and our writing.

However, it is exactly deplorable to do mathematical calculations when time is represented in cycles. (It's possible… but it's also exactly deplorable.)

When dates get represented as numbers, they need to act like numbers. For example, if I'm travelling on the highway and if I'm at mile marker 269 and if I know my destination is at mile marker 116, I can easily subtract the two numbers to know that my destination is 153 miles away. This makes sense because… that's… just how numbers work!

Suppose that I want to know how much older Patricia is than Elisa. Using the numbers on our table, we can subtract Elisa's birthday (20150531) from Patricia's birthday (20150528). Doing the math tells us that Patricia is three days older than Elisa. Perfect right?

No! Let's try that again, except, let's see how much older Lance is than Nicholas. By doing the same subtraction, we get 289,616 days—which is close to 793 years. I happen to know that Lance was twenty-eight when Nicholas was born. Why did the math work for Patricia and Elisa but not for Lance and Nicholas? It's because Patricia and Elisa were both born in the same month—May of 2015. Lance and Nicholas weren't. This system has gaps in it—literally. For instance, the difference between 20171231 and 20180101 is only one day. However, if we subtract the two numbers, we get 8,870 and not… one (you know, kind of like we should). Also, what about a date like 20171581? That's not even a date that ever existed, but what would a computer program even do if it was given that input?

We want the dates to work the same way that our mile markers worked. In order to get that to happen, we have to abandon notating our dates in cycles and put them on a linear scale like the mile markers. (I do have to point out that the mile-marker analogy breaks down as soon as we realize that the mile markers reset at the state line.)

This is the way that most software that calculates dates already works! If you're a spreadsheet person, try this: open up your spreadsheet software—Excel, LibreOffice or, if you don't have any of these, Google Sheets. Select a cell and type in today's date (or press Ctrl+;). Select another cell and type in your birthdate. Select now a third cell and create a cell to subtract your birthdate from today's date. Once you press enter, the result that you should see is the number of days old you are. Divide that by 365.2425, and you should get your age in years.

The reason this works is because each cell in a spreadsheet has a value and a format. The format that you see is the date represented in the cycles that we humans are used to (i.e., years, months, days). However, the value that's in the cell is a number. If you want to see this number, select a cell and then change the number format for the cell to ‘General’ or ‘Automatic’. (This process is different in different spreadsheet programs.) It goes the other way around. Type ‘37045’ into a cell. Now, change the number format of the cell to a date. You'll end up with 2001 June 3.

If we take that table of dates from above and put it in a spreadsheet, we'd end up with this data:


While that's what the spreadsheet will actually store in the background, we can give the spreadsheet instructions to format the data however we want. It will convert the dates from these linear formats into the cyclical representations that we humans deal with. How those conversions are done is a topic for another time, but these dates on a linear scale are an improvement. Remember how I said that mathematical calculations are exactly deplorable with cyclical representations? Well, now, mathematical calculations are elegant and straightforward. Need to know what date it will be a hundred days from now? Tell the spreadsheet to add the days and let it do the math for you!

Tags: Calendars, Mathematics, Software Development

Add Comment

If you would like to comment on something that you read, by all means, leave a note here. Please note that all comments are approved before being displayed to prevent spam comments.