Month arithmetic in R, a quick guide

Base

I started my search in the {base} package by looking at the documentation for difftime. The tell was in the function signature

difftime(time1, time2, tz,
     units = c("auto", "secs", "mins", "hours",
               "days", "weeks"))

(No units for months.)

lubridate

Next, I tried the lubridate package ¹, and it seemed to provide a way forward. From the documentation:

Adding months frustrates basic arithmetic because consecutive months have different lengths. With other elements, it is helpful for arithmetic to perform automatic roll over. For example, 12:00:00 + 61 seconds becomes 12:01:01. However, people often prefer that this behavior NOT occur with months. For example, we sometimes want January 31 + 1 month = February 28 and not March 3. %m+% performs this type of arithmetic. Date %m+% months(n) always returns a date in the nth month after Date. If the new date would usually spill over into the n + 1th month, %m+% will return the last day of the nth month (rollback()). Date %m-% months(n) always returns a date in the nth month before Date.

However, the arithmetic calculations required datetime objects (dates, plus times of day) required arguments in the %m-% operators to be of different classes. During several hours of tinkering, it seemed close to doing what I was looking for, but perhaps unattainable.

mondate

Further digging led me to mondate² Its documentation notes

A mondate represents a date as a numeric equalling the number of months since the beginning of the current millennium (the “mondate epoch”). Somewhat arbitrarily, and at the risk of reopening a decade-old debate, “the beginning of the current millennium” is defined as the instant between December 31, 1999 and January 1, 2000. The need for a “mondate” class arises in the area of actuarial analysis, and other areas of financial modeling that need to reconcile to a company’s book of accounts. Its motivation is based on the following presumptions: 1. Business accounting-wise, the closing of the books for a month, quarter, and year are the important milestones for measuring time. 2. For accountants – and actuaries – it is usually not important to measure events on an hourly basis. 3. All events that occur during a business day, up to and including the closing of the books for a day, are all “accounted for” as having occurred “at the same time.” To appreciate the difficulty in measuring the passage of time in days, note that there are typically three fewer days in the first half of the year (January 1 through June 30) than there are in the second half. Yet accountants will say that on June 30th the year is half over. For another example, note that – with the exception of July/August and December/January – the same days of the month for two consecutive months are not one “month” apart if measured in days because, with those exceptions, consecutive months contain differing numbers of days, so which of the two months do you choose as the yardstick? Since changes in accounts over the course of a month, quarter and year are the amounts by which financial results are measured, it is important to be able to measure the passage of time where a year is comprised of twelve months of equal “accounting weight.”

A mondate is simply a real number whose fractional part represents the fraction of the month as of the end of the day. E.g., the fractional part of January 1st = 1/31; the fractional part of February 1st = 1/28 or 1/29, depending on the year. A mondate which is a whole number (i.e., no fractional part) corresponds to a month that is fully completed, whose subsequent month has not yet begun; i.e., the instant in time between one month and the next.

It took a while to digest that, but it seemed promising. The key difference with other approaches is that mondate measures in months from the beginning of its epoch, rather than seconds. This may be relevant in light of the pending GAAP change in 2019 on revenue recognition.

I don’t care what day you recognized that income in April, I just want to be sure that it was April and not May

Here’s a toy example. One important aspect it ignores is Inf values arising from division by zero that came up in my motivating example. Another thing to note is that the package has objects that have to be converted to numeric objects and, in some cases, rounded up. Those are relatively minor and easy to implement.

>     library(mondate)
>     library(lubridate)
>     start  = "2018-02-01"
>     end = "2018-09-01"
>     fully_inclusive <- function (start, end) {
+         start <- mondate(start)
+         end <- mondate(end)
+         open_interval = (end - start) + 1
+         return(as.numeric(round(open_interval,0)))
+     }
> fully_inclusive(start,end)
[1] 8

So, now we’ve looked at something that started on February 1 and finished on September 1 and correctly concluded that eight months were involved.