This Page: www.cpearson.com/excel/FractionalMonths.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 02-Aug-2017

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

Fractional Months

This page describes considerations when working with fractional months.

Introduction

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.

Calculating The Fraction Portion

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. |