Every so often, an Excel formula challenge comes up that takes more than a little dissecting and pondering before the solution becomes clear. I had one of these recently, and when I finally did hit on the answer, I even surprised myself. I’ll get to that in a bit, but first a little backstory.
One of my job functions was to create a Policies and Procedures manual explaining all of the workings of the place from my perspective, and to keep it updated. It’s currently somewhere around 70 pages, and to make it easy for the user, it’s a Word Doc that is extensively cross-referenced with a full glossary of the many complicated terms in the Medicare insurance business. There are also a number of appendices, from the very inside baseball explanation of the various Medicare Supplement Plans to a useful but very specific guide to the nearest fast and fast casual dining establishments relative to the office which links out to Google Maps for each destination.
Another inclusion was a schedule of pay periods and pay dates which I originally included as a quick and simple table cut and pasted in from Excel starting with the pay period from when I originally created it. But it was static and although it covered a couple of years, would eventually go out of date.
So my challenge was this: How to create a dynamic table in Word by linking to an entire table in Excel that would always start during the particular pay period the document was opened in. For example, as I write this, our current pay period goes from February 3rd to 16th, with payday on the 21st. But if someone were to open the document in a week, then the first entry should show February 17th to March 2nd, and so on.
The three columns in the table show just that for each pay period: the start date, the end date, and payday. Each row below shows the next period, etc.
Normally, this would be a simple matter of doing an IF/THEN calculation. IF (today’s date) is greater than a period’s start date and less than its end date, then use the start date in that cell, otherwise increment to the new date.
Now, this would be great if I could create a table of all the start dates for however many years and then link to it. Unfortunately, that wasn’t an option.
The other usual method for incrementing periodic changes wouldn’t work here at all. Normally, you could look at the cell above and use something like “IF (old cell) is less than 10, (this cell) equals (old cell) + 1, otherwise (this cell) equals 0.” Then (this cell) becomes (old cell), repeat.
The problem is that you can quickly run afoul of the circular reference problem,
This happens when two or more cells have formulae set up so that each one provides input to the other at the same time. The simplest example would be something like inputting “=A1+B1” in cell A1. This is telling Excel that the value in cell A1 is equal to itself plus B1. The problem is that B1 changes the value of A1 no matter what it is, so A1 has no value until B1 does, but even if B1 has a value, it will keep constantly changing the value of A1. It all leads to an endless loop, and computers do not like endless loops. That’s why program like Excel have a hard-coded braking system that will stop an endless loop before it happens. If you ever accidentally do enter one, you’ll notice that the program gives you an error message, draws little arrows to point out the offending cells, and evaluates the initial culprit to zero.
Which is the lone way around of saying that I had to figure out a way to calculate the current pay period start date based on the current date and do it all in one cell without evaluating any outside expressions.
Excel nerds, if you’d like to go grab a nosh or latte and think about this now, please do and compare your answer later. Everyone else, here’s how I managed to figure the problem out — again, after a lot of thought and contemplation.
The problem, restated: Calculate a table of payroll period start, end, and pay dates dynamically based on the current date, and without relying on any kind of lookup function using a pre-determined database. In other words, this puppy had to do it dynamically, with only one input point.
When I finally found the solution, I damn near shit my pants in joy, because it was really so simple and elegant, but it took a lot of thought, and gets to the heart of how Excel handles dates.
The only way that a computer can work with dates is to count them as a certain number of days since a fixed day. The behind the scenes work converts a particular number into a particular date. The catch is that a system can only handle dates after their zero point, and not before. If you enter 08/01/1899 into any Windows or Apple program, it won’t know what to do with it. Start with any date on or after 01/01/1900, though, and you’re fine, because that’s the start point.
So… in Excel, any date is just the number of days since that start point, and if we go with the first start date of my company’s payroll period in 2020, we get January 6 which, in the terms stated, is 43,836. If you don’t believe me, divide this number by 365.25, and you get 120.02, which is just a hair over the number of years it’s actually been.
But forget that. The important number is 43,836, because, to Excel, that means the same thing as Epiphany, which is January 6, 2020 — the start of a pay period where I work.
The key insight I had here was this: The important bits were those days that were exactly increments of 14 days after the starting point — and having a starting point and increment meant that I suddenly had two constants to plug into the equation, and that made all the difference, because constants are the anchors that everything else could be hung on, and it could happen in a single source cell.
Two constants would be modified by a single variable — what is today’s date? And the answer to that question comes in the form of another great Excel function, NOW(), which simply returns the number for the date at the point it’s invoked.
So… given an arbitrary pay period start date of January 6 and a pay period of 14 days, the first cell formula looks like this:
Where 43836 is the constant date value for January 6, 2020, 14 is the constant interval in days, and NOW() is the variable based on today’s date. Inside the brackets, we get the integer value of Today minus the start date divided by the interval, and then multiplied by the interval outside of the integration.
What all that fancy math does is this: decides whether the current date is evenly divisible by 14. If it’s not, then it uses the original start date. If not, it increments it by a number that just happens to work out to be the actual number of increments since that first day. If it’s not obvious, it works like this. The +(INT(((NOW()-43836)/14) part determines how many pay period past the original date that NOW is. The *14) part puts back the days to land on the right week.
Yes, it was a struggle to figure it out and it worked beautifully, and I felt that my Excel Jedi score was vastly boosted. But… oh. Did I mention that I had to make this whole thing work as a dynamic spreadsheet insert inside of a Word Doc with no external links to Excel?
Oops, I guess? I did it again.