Fractional Months
This page describes considerations when working with fractional months.
In some applications, users need to calculate a date interval in fractional months. While this
may seem simple, there are considerations you must keep in mind, specifically because a month may
contain anywhere from 28 to 31 days. You can use the DATEDIF function
to get the number of whole, calendar months between two dates (for example,
=DATEDIF(Date1,Date2,"m")), but to get the fractional portion, you need
to choose one of three methods, and be consistent in all your calculations. These methods are
- Use the number of days in the start month as the basis for the number of days in a month.
- Use the number of days in the end month as the basis for the number of days in a month.
- Use 30 as the basis for the number of days in a month.
Each of these methods will return a different result, each of which can be considered correct depending
on the context.
As noted earlier, you can use the DATEDIF function to get the number of whole
months between two dates. The following formulas calculate the fractional portion only, so you would
add the results of these formulas to the result of DATDIF to get the full difference
in fractional months between two dates.
You can use the number of days in the start month as the basis for the number of days in a month.
The following formula will do this.
=DAY(EndDate)/DAY(DATE(YEAR(StartDate),MONTH(StartDate)+1,0))
If the start date is 1-Jan-2009 and the end date is 15-Feb-2009, the fractional portion, based on a 31
day month, is 0.48387. In this formula, the fractional portion will not vary if the end month is modified.
You can use the number of days in the end month as the basis for the number of days in a month.
The following formula will do this.
=DAY(EndDate)/DAY(DATE(YEAR(EndDate),MONTH(EndDate)+1,0))
If the start date is 1-Jan-2009 and the end date is 15-Feb-2009, the fractional portion, based on a 28
day month, is 0.53571. In this formula, the fractional portion will vary if the end month is modified.
You can use a fixed number of days for the number of days in a month, such as 30 days.
The following formula will do this.
=DAY(EndDate)/30
If the start date is 1-Jan-2009 and the end date is 15-Feb-2009, the fractional portion, based on a 30
day month, is 0.5000. In this formula, the fractional portion will not vary if the end month is modified.
As you can see, the entire concept of a "fractional month" is a bit slippery and ambiguous. As a general
rule, you should try to design your application so that it does not need to use fractional months. However,
if you do need fractional months, decide on the method you are going to use to calculate the
fractional portion and use that method consistently throughout your application.
 |
This page last updated: 19-March-2009. |