The Vince Files

Anna was doing some work tonight in one of her spreadsheets and needed a bit of help. One of the things she was doing was calculating the number of months between 2 dates. There isn’t really an easy way to do this in Excel without resorting to using its VBA macro language. Anna found an undocumented function in Excel called “DateDif” (one “f”). Unfortunately, this function is undocumented and will not show up in the list of Date and Time functions.

Basically, this function takes three parameters:

=DateDif(Date1, Date2, Interval)

where Date1 and Date2 are dates and Interval is the interval type (e.g. “m” for months).

The calculation is simple: Find the DateDif between 2 dates to find the number of months and add 1 to make it inclusive. But the catch is that if the start date is before a minimum start date, ignore the start date and use the minimum start date instead. Likewise, if the end date is after a maximum end date, use the maximum end date instead. Well, this proved unwieldy since our simple one-line function started stretching into something like:

=DateDif(if(Date1<MinDate, MinDate, Date1), if(Date2>MaxDate, MaxDate, Date2), Interval)

Remember, the “if” function takes 3 parameters as well: =if(logical_condition,value_if_true, value_if_false).

What did I do? I decided to write a simple macro instead using good old “ActiveCell.Value” and “ActiveCell.Offset”, etc. There’s a lot going in that function and it’s a lot easier to break things up onto separate lines and step through the code as it executes.