Calculate number of months between 2 dates

[john liem]

I have a Start Date and an End Date, need to calculate the number of
months in between. Conditions: Start date <=15th includes the month,
End date >= 15th includes the month.
Examples: between 14-04-05 and 15-06-05: 3 months, between 16-04-05 and
13-06-05: 1 month, between 16-02-04 and 03-05-05: 14 months. Thank you.
John


--
john liem

[JE McGimpsey]

One way:

=12*(YEAR(B1)-YEAR(A1))+MONTH(B1)-MONTH(A1)+1-(DAY(A1)>15)-(DAY(B1)<15)

In article <john.liem.1px0ts@news.officefrustration.com>,
john liem <john.liem.1px0ts@news.officefrustration.com> wrote:

> I have a Start Date and an End Date, need to calculate the number of
> months in between. Conditions: Start date <=15th includes the month,
> End date >= 15th includes the month.
> Examples: between 14-04-05 and 15-06-05: 3 months, between 16-04-05 and
> 13-06-05: 1 month, between 16-02-04 and 03-05-05: 14 months. Thank you.
> John

[Daniel CHEN]

Use the following formula:

=(YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1)-1+IF(DAY(A1)<=15,1,0)+IF(DAY(A2)>=15,1,0)

assume A1 is starting date and A2 is ending date.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
UDQServices@Gmail.com
www.Geocities.com/UDQServices
Try UDQ Consulting Services - Your "Impossible" Task Could Be Someone Else's
"Piece of Cake"
===== * ===== * ===== * =====




"john liem" <john.liem.1px0ts@news.officefrustration.com> wrote in message
news:john.liem.1px0ts@news.officefrustration.com...
>
> I have a Start Date and an End Date, need to calculate the number of
> months in between. Conditions: Start date <=15th includes the month,
> End date >= 15th includes the month.
> Examples: between 14-04-05 and 15-06-05: 3 months, between 16-04-05 and
> 13-06-05: 1 month, between 16-02-04 and 03-05-05: 14 months. Thank you.
> John
>
>
> --
> john liem

[Simon Cleal]

Alternativly use the DATEDIF function

=DATEDIF(A1,A2,"M")

The "M" indicates that you want the answer in Months...see Help for more info


Simon Cleal
*******************************************************
"The computer allows you to make mistakes faster than any other invention,
with the possible exception of handguns and tequila" - Mitch Radcliffe
*******************************************************

[JE McGimpsey]

You probably should have tested this - DATEDIF() doesn't meet the
criteria in the problem statement.

For instance, in the example the OP gave:

A1: 14 Apr 2005
A2: 15 Jun 2005

DATEDIF returns 2, while by the OP's rules, the result should be 3.

In article <B8D499C0-8F41-41EA-A8EE-AC1C1710939F@microsoft.com>,
"Simon Cleal" <SimonCleal@discussions.microsoft.com> wrote:

> Alternativly use the DATEDIF function
>
> =DATEDIF(A1,A2,"M")
>
> The "M" indicates that you want the answer in Months...see Help for more info