|Date Difference family|
|Functions that work out differences between dates|
|Description: This family works with differences between two dates.|
|Introduction: The Date Difference Family is for calculating the difference between two dates, in either months or years. |
The four functions in this family have two mandatory arguments (Date1 and Date2, the dates, which will as much as many of us need to know for determining the number of years, months or days between two dates.
The final two function arguments are concerned with how the date difference is calculated. Believe it or not, there are a myriad of ways to do this (See Using Daycount) and all of us are familiar with a couple of them (Actual/365, 30/360 etc).
If you default DayCount and Periods then the function will use Business Functions ACTM/ACTM decimal month method, a method unique to us but very broadly useful. It simply says that a month is always 1/12 of a year and the fraction of a month is the number of days elapsed in that month divided by the number of days in the month. It"s simple and useful for a wide range of business applications, does not suffer from the inaccuracies that any of ACT methods can have when dealing with periods of several years, and is more comprehensible than 30/360 (which has at least two sub-definitions within it).
The purpose of DayCount and Periods being there is, of course, so that you can use them to firmly determine how the date difference should be calculated, and here you have the full panoply of daycount options available to you.
The DiffD function sometimes causes controversy - surely the difference in days is just one Date less the other? Well, the answer is no and it can be demonstrated by looking at say 30/360 mode where there are strict rules in any standard text book on for how you establish the difference in days between the 2 dates, prior to dividing by 360.
The most common and to many the most perplexing daycount method, ACT/ACT (in period) is also available in these functions, and is the daycount method used in quite common business situations like rent and bond (but not necessarily loan) interest. This method determines the number of periods and fractional periods (done on a strict day total basis) between 2 dates. Then, if you want to turn that number of periods into a fraction of a year, you divide by the number of periods in a year. This is what the function DiffP does, and this function really exposes what ACT/ACT (in period) is all about. So if you wanted the number (including fractions) of rental payments between two dates, or the accrued bond interest, you can use a function as simple as DiffP before resorting to specific functions like Interest or Con.
|Functions in the Date Difference family (4)|