Calculating the number of months & days between two dates
published: Fri, 15-Aug-2003 | updated: Sat, 6-Aug-2016
The question on a newsgroup generally goes like this, "I want to calculate the number of months between two dates. How do I do it?"
Algorithms from not thinking about the problem
The general answer goes something like this. Calculate the month and year for each date. Subtract the month values and the year values. Multiply the difference in years by 12 and add to the difference in months to give the result. We'll call this algorithm A.
Let's check the algorithm out with a couple of examples: What's the difference in months between 15-Jan-2003 and 15-Jul-2004? Well January is month 1, and July, month 7, so their difference is 6. The difference in years is 1, which makes 12 months, therefore the total difference in months is 18. Which, luckily, is what we expect by counting on our fingers.
Another example: the difference between 15-Jul-2003 and 15-Jan-2004. The difference in months values is -6, the difference in year values, 1 (or 12 months) making a total of 6 months difference. Again what we'd expect using our trusty fingers.
The final example is this one: the difference between 31-Jan-2003 and 1-Feb-2003. The answer, using this simplistic algorithm, is one month, totally counter-intuitive to common sense since it's only one day, for heaven's sake.
So a better algorithm might be to do the same thing, but to return the number of days as well. At least then the developer can decide whether to ignore the number of days, or to round it into the number of months, or whatever. Let's investigate this possibility.
I got an example of this algorithm from a not-very-good Visual Basic "helper" site. We'll call it algorithm B. Keep a running date, and set it initially to the earlier date. Continue adding one month to the running date until it exceeds the second date. Count the number of months to just before that happens. Now add one day to the running date until you reach the second date. The number you have to add is the number of days you return. Sounds pretty good, eh?
Let's try it. What's the number of months and days between 1-Jul-2003 and 15-Jan-2004? Start at 1-Jul-2003, and start adding months. Add 1, to get 1-Aug-2003. Add another to get 1-Sep-2003. And another to 1-Oct-2003, and so on until you reach 1-Jan-2004 (having added 6 months). Adding another month would exceed our second date. Now add in the days, one at a time, until you reach 15-Jan-2004. You'll find that the answer returned in 6 months, 14 days, which is what our intuition and the calendar would also say.
Another example: the number of months and days between 30-Nov-2002 and 30-May-2003? Add a month, 30-Dec-2003. Add another, 30-Jan-2003. Add another, 28-Feb-2003 (there is no 30-Feb of any year). Add another, 28-Mar-2003; and another, 28-Apr-2003; and another, 28-May-2003. Now add one day twice to get 30-May-2003. That's the answer from Algorithm B: there are 6 months and 2 days between 30-Nov-2003 and 30-May-2003. This is totally against our intuition and is utterly wrong.
Another interpretation of this calculation I've seen takes the day, month and year differences and computes the answer from those. The problem occurs should the number of days be negative: you somehow have to add in a month's worth of days, and subtract a month from the month total. Should that be 30 days, perhaps? (Algorithm C1) Or should it be the number of days in the previous month to the later date? (Algorithm C2) I've seen both of these, and both are non-optimal. A breaking example for the first is 31-Mar-2003 to 30-Apr-2003; for the second, 31-Jan-2003 to 30-Mar-2003.
Thinking about the problem
No, the best bet is to start again from scratch and think about the problem afresh.
The first observation is that I cleverly produced all my "breaking cases" by considering the end of the month, especially where February is concerned. Suppose that both dates have day parts that are less than 28, in other words are between 1 and 27 inclusive. I think we can all agree that calculating the number of months and days using algorithm B would always produce the correct answer. That's not to say it cannot be improved -- and indeed we've seen that it must -- but it's good as a starting point for discussion.
So what about those pesky days at the end of the month? This is where we need some lateral thinking. Quick: what is the number of months between the last day of March and the last day of April? I think you'd naturally say 1 month (and not 30 days as an alternate answer). Between 31-Jan-2003 and 28-Feb-2003? One month again. The principle seems to be: if the two dates are at the end of their respective months, just count the number of months between them.
Now a more difficult question: what's the number of months and days between 30-Apr-2003 and 30-May-2003? zero months and 30 days, or one month and zero days? Not so easy. You could imagine that, on the one hand this is just like calculating the number of months between the 1st of one month and the 1st of the next (that is, one), but on the other hand one month from the end of a month is the end of the next. There is no correct answer, I'm afraid. I can imagine scenarios where either could apply.
So we don't make the decision to force it one way or another in our generic "calculate the number of months" routine, instead we decide which version we need at the time we make the calculation (or we decide to always use one over the other in our application).
Wrapping it all up in an implementation
The algorithm we'll use is a variant of algorithm B. Instead of continually adding 1 month to a running date, we keep a running number of months and continually add it to the start date. In other words: set the running count of months to 0; calculate the start date plus the running count of months; if it is less than the second date, add one to the running count and try again. If the calculated date equals the second date, we're done (and we have the correct number of months in the running count). Otherwise the calculated date is greater than the second date, so we subtract one from the running count, calculate the date that number of months from the start, and calculate the number of days between that and the second date (which is always accurate and without ambiguity).
Indeed, if you think about it, this algorithm is the only unambiguous way to calculate the number of months between two dates; and it's all because a month is not a constant length in days.
The problem thus boils down to calculating a date so many months from another date. As discussed above, there are two possible ways we could go, and both depend on whether the first date is at the end of the month or not. Suppose it is not. Adding a number of months to this date is a matter of doing the obvious mod thing. We don't change the day number, unless it would mean that the target date is meaningless (31-Jan plus one month equals 31-Feb, which is invalid) in which case we force the day to be the end of the month. Now suppose that the start date were at the end of the month. We have two choices: either do the calculation as I've just described, or stick to the ends of the months. I call the latter option the "sticky" option: month ends stick.
At that point we've completed our design. We write a method that adds a number of months to a date. This routine should also take a parameter that defines whether the calculation should include sticky month ends or not. We can then write a method that calculates the number of months and days between two dates by using the first.
Luckily, those awfully nice people in the .NET Framework building have already written an AddMonths() method for DateTime instances. Five minutes writing a test harness convinced me that AddMonths() implements the obvious mod calculation: add the months to the month of the start date (mod 12), if the resulting date is invalid, force the day value of the result to the last day of the month. So, all we have to do is to implement the sticky code.
public static DateTime DateAddMonths(DateTime startDate, int monthCount, bool sticky) {
DateTime result = startDate.AddMonths(monthCount);
if (sticky) {
if (DateTime.DaysInMonth(startDate.Year, startDate.Month) == startDate.Day) {
int resultYear = result.Year;
int resultMonth = result.Month;
result = new DateTime(resultYear, resultMonth,
DateTime.DaysInMonth(resultYear, resultMonth));
}
}
return result;
}
public static void DateDiffMonths(DateTime date1, DateTime date2, bool sticky,
out int months, out int days) {
// ensure that date1 <= date2
if (date2 < date1) {
DateTime t = date2;
date2 = date1;
date1 = t;
}
// make a guess at the answer; using 31 means that we'll be close but won't exceed
int monthCount = ((date2 - date1).Days / 31);
// find the maximum number of months that's less than or equal to the second date
DateTime testDate = DateAddMonths(date1, monthCount, sticky);
while (testDate < date2) {
testDate = DateAddMonths(date1, ++monthCount, sticky);
}
// if we've hit the exact date, return the number of months and zero days
if (testDate == date2) {
months = monthCount;
days = 0;
return;
}
// otherwise we exceeded the second date, back up and return the correct values
testDate = DateAddMonths(date1, --monthCount, sticky);
months = monthCount;
days = (date2 - testDate).Days;
}
Summary
Now, although the algorithm I gave calculates the number of months and days from the first date, we could imagine a different algorithm that did the same counting backward from the second date. For example from 15-Mar to 30 Apr is one month 15 days, going backwards with sticky month ends is 1 month 16 days. Another variant would be to calculate the number of complete calendar months and then the number of days on either end. So for 15-Jan to 15-Mar would be one month 31 days (the one month being February, of course). I'd have to say that I haven't actually seen this variant in practice; the vast majority of times I've had to calculate the number of months between two dates, it's been obvious that the calculation proceeds from the earlier date.
Maybe now this article is on the web we can forget all these other, invalid, algorithms.
