Gallery

Snoozing at the OASIS

Posted on 28 Jan 2009 at 17:07

This month, Simon Jones looks into the maintenance of the ODF standard, and sizes up the new Outlook add-in Xobni.

DateDif() calculates the number of days, months or years between two dates, and its syntax is DateDif(startdate, enddate, unit). The start and end date parameters can be text strings, date values or references to cells containing date strings or values. The unit parameter must be a text string, or cell reference to a text string that determines what information is returned.

DateDif() is only included in Excel and SharePoint for compatibility with Lotus 1-2-3, and its results are readily attained by using straight subtraction combined with the Year() and Month() functions, so it doesn't really add anything useful. Since dates are stored in Excel as days elapsed since 01/01/1900, the number of days between any two dates is just one date subtracted from the other. The number of years between two dates is similarly easy to work out by subtracting the results of the Year() function on both dates, for example =Year(D2)-Year(D1). The number of months between two dates is 12 times the number of years plus the number of months, for example =(Year(D2)-Year(D1))*12+Month(D2)-Month(D1). This works even if the month of the end date is earlier than the month of the start date, so May 2002 from March 2006 gives four years plus minus-two months, in other words three years and ten months, or 46 months.

The "YM" variant of the DateDif() function is just the subtraction of the month values, as in =Month(D2)-Month(D1), and the "MD" variant is similarly just the subtraction of the Day values as in =Day(D2)-Day(D1). The "YD" variant is achieved by first calculating the number of days between each date and the start of its year, and then subtracting the results, so =(D2-Date(Year(D2),1,1))-(D1-Date(Year(D1),1,1)).

If you want to ignore weekends and other holidays use the NetWorkDays() function, which takes two dates and, optionally, a range of holiday dates then calculates the number of working days between them. Unfortunately, all versions of Excel produced so far assume that the weekend days are Saturday and Sunday, which makes it useless in the Middle East, where the weekend days are either Thursday and Friday or Friday and Saturday (Iranians only get Friday off, generally working Saturday to Thursday). This cultural short-sightedness is likely to be fixed in the next version of Excel, as it was one of the shortcomings highlighted by the ISO when it was standardising the OOXML file formats.

There's also a YearFrac() function that can be useful - it returns a floating-point number representing the number of years between two dates. This function takes two dates as parameters and an optional Basis number, which changes how it counts days per month and days per year. The true fraction of a year is obtained by setting Basis=1, which uses the actual number of days for each month and days per year from the date parameters. However, some financial and statistical users will require fictions, such as that there are 30 days in every month or 360 or 365 days in every year, thus smoothing out the irregularities of the actual calendar - these are achieved by four other Basis values, as shown in the table 2. The NASD mentioned under Basis 0 is the US National Association of Securities Dealers - since succeeded by the US Financial Industry Regulatory Authority - and is the same NASD that lent its name to the NASDAQ stock exchange. See http://en.wikipedia.org/wiki/Day_count_convention for more details of the different conventions.