calculate month

[Guest]

How can I calculate the number of months (or years) between two dates, such as
06/11/2000 and 09/20/2005?

 

[Don Guillett]

Use the datedif if tookpak installed

 

[Guest]

RJ --
In the last response, don't use 30.4 -- that's days in a month. Use 12 --
months in a year.

My bad.

 

[Guest]

RJ --
If the start date is in A1 and the end date is in B1, I'd calculate months
like this:
=INT(YEARFRAC(A1,B1)*30.4)

There may be an easier way to calculate years, but I'd do it like this:
=YEAR(B1) - YEAR(A1)

hth

 

[Guest]

Sorry, it's not installed. Any other thoughts on a formula/function I could
use?

 

[Peo Sjoblom]

You don't need the ATP, just use it as

=DATEDIF(start_dat,End_Date,"m")

 

[Ron Rosenfeld]

How can I calculate the number of months (or years) between two dates, such as
06/11/2000 and 09/20/2005?

=DATEDIF(start_date,end_date,"m")

--> 63 with your data.

Of course, you have to be aware that when you are dealing with dates near the
end of the month, and the end_date month does not have as many days, you may
get unexpected answers.

For example:

start_date 29 Jan 2006, 30 Jan 2006 or 31 Jan 2006
end_date: 28 Feb 2006

Result: 0 months

Depending on exactly what you want, this may or may not be adequate.


--ron

 

[Bill Johnson]

I've been trying to calculate the number of months (including fractions of
months) between two dates. The closest I've come is using YearFrac.

Date1 = 01/01/2006 - Cell A1
Date2 = 11/30/2011 - Cell A2

YearFrac(A1,A2)

Result = 5.914 (Years) - Cell A3

Months = A3*12

Result = 70.97 (Should be 71)

I think YearFrac is the closest formula but it's not always perfect. Any
way to make it work right every time?

Bill Johnson

 

[Glenn]

I've been trying to calculate the number of months (including fractions of
months) between two dates. The closest I've come is using YearFrac.

Date1 = 01/01/2006 - Cell A1
Date2 = 11/30/2011 - Cell A2

YearFrac(A1,A2)

Result = 5.914 (Years) - Cell A3

Months = A3*12

Result = 70.97 (Should be 71)

I think YearFrac is the closest formula but it's not always perfect. Any
way to make it work right every time?

Bill Johnson

Define "between two dates"...

- Including both Date1 and Date2
- Including only Date1
- Including only Date2
- Including neither

 

[Glenn]

Define "between two dates"...

- Including both Date1 and Date2
- Including only Date1
- Including only Date2
- Including neither

Based upon your desired results above, it looks like you mean the first
(including both dates). Therefore, either of the following should get you
closer to the answer you want:

=YEARFRAC(A1-1,A2)*12

or

=((YEAR(A2)-YEAR(A1)-1)*12)+(12-MONTH(A1))+(MONTH(A2)-1)+((DATE(YEAR(A1),MONTH(A1)+1,1)-A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))+(DAY(A2)/DAY(DATE(YEAR(A2),MONTH(A2)+1,0)))