G
Guest
How can I calculate the number of months (or years) between two dates, such as
06/11/2000 and 09/20/2005?
06/11/2000 and 09/20/2005?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Don Guillett
Use the datedif if tookpak installed
G
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.
In the last response, don't use 30.4 -- that's days in a month. Use 12 --
months in a year.
My bad.
G
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
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
G
Guest
Sorry, it's not installed. Any other thoughts on a formula/function I could
use?
use?
P
Peo Sjoblom
You don't need the ATP, just use it as
=DATEDIF(start_dat,End_Date,"m")
=DATEDIF(start_dat,End_Date,"m")
R
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
B
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
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
G
Glenn
Bill said: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
G
Glenn
Glenn said: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)))