?
Solved

Excel formula that tells you the number of months

Posted on 2013-05-20
15
Medium Priority
?
366 Views
Last Modified: 2013-05-21
Hi,

I have an Excel spreadshet that list in a column the acquisition dates. I need to know the number of months from the acquisition date to June 2012 for each acquisition date. Below is a sample of the column with the acquisition dates. I added the months for the first acquisition date and I came up with 44 months. How do I do this with a formula?

Acquisition Date            How many Months to June 2012?
(YYYY-MM-DD            
2008-11-14            Should be 44 months
2009-07-15            
2010-01-01
0
Comment
Question by:Conernesto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 39182736
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 39182739
Use this formula:

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

Where B1 contains the date in June, 2012, and A1 contains the acquisition date.

Kevin
0
 

Author Comment

by:Conernesto
ID: 39182785
How do I enter the B1 date?

My acquistion date is on celll A1 and it's equal to "2008-11-14"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39182790
=DATE(2012,6,1)

Kevin
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39182800
Hi,

I see 43 months using this in-cell formula:

=DATEDIF(A3,DATE(2012,6,DAY(A3)),"m")

Where [A3] is the cell that contains 2008-11-14.

(See attached)

BFN,

fp.
Q-28133886.xls
0
 

Author Comment

by:Conernesto
ID: 39182808
Is there a way to increase this by one so we get 44 months?
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39182813
=DATEDIF(A3,DATE(2012,6,DAY(A3)),"m")+1

:)
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39182821
Use this date in B1:

=DATE(2012,6,30)

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39182826
A note about my solution. I prefer not to use DATEDIF because it's support by Microsoft has been spotty and it has bugs. The DATEDIF function was introduced in Excel version 5.0 as a 1-2-3 compatibility feature. It was not documented until Excel 2000 and was subsequently dropped from the help in Excel 2002. It is a built-in function that operates just as any other built-in function operates. It does not produce correct results if the start date is the 29th or later and the end date is in the month following a month with fewer days than in the day of month in the start date. For example, if the start date is January 31, 2006 and the end date if March 1, 2006, the MD value is -2.

Kevin
0
 

Author Comment

by:Conernesto
ID: 39182836
Kevin,

I will use your first answer I replaced the date for B1 with = Date(2012,7,30) instead of =Date(2012,6,30) and I go the first answer that I was looking for "44".

Thank you.

Conernesto
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39183501
You're welcome.

PS. I have never had any trouble using the DATEDIF() in-cell function, nor the Visual Basic for Applications equivalent, DATEDIFF(), that is documented within the help file for Microsoft Excel 2003 (my version of Excel of choice).
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39184138
For an illustration for why, like Kevin, I almost never use DATEDIF, enter these in an Excel worksheet:

A1: 1/31/2006
B1: 3/1/2006
C1: =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days"

You get the following non-sensical result:

0 years, 1 months, -2 days

Or this example, with a result that is less nonsensical, albeit still (IMHO) wrong:

A1: 1/29/2006
B1: 2/28/2006
C1: =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days"

Returns: 0 years, 0 months, 30 days

Also, please note that despite the similarity in names, DATEDIF and DateDiff use a completely different method to do calculations.  Consider:

A1: 12/31/2012
B1: 1/1/2013
C1: =DATEDIF(A4,B4,"y")

That will return zero.  However, DateDiff using the same parameters will return 1, because while DATEDIF is attempting to measure the number of "full" intervals between two dates, DateDiff counts only the time interval boundaries between two dates.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39184257
Conernesto,

It would help if you provided an explicit definition for how you want to count the months.

Patrick
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39184333
...while DATEDIF is attempting to measure the number of "full" intervals between two dates, DateDiff counts only the time interval boundaries between two dates.

As long as you are aware of the difference, & make an allowance for this (like simply adding one to the total, as above, or subtracting one from the total, if required), usage is possible.

PS. In your examples, Patrick, I would simply use C1=B1-A1 to provide the number of days between the two dates.

PPS. For completeness, Chip Pearson has a "gotcha" note on the use of =DateDif(...) when using dates spanning leap years:

[ http://www.cpearson.com/excel/datedif.aspx ]
---
DATEDIF And Leap Years

When calculating date intervals, DATEDIF uses the year of Date1, not Date2 when calculating the yd, ym and md intervals. For example,

=DATEDIF(Date1,Date2,"md")

returns 28 for Date1 = 1-Feb-2007 and Date2 = 1-March-2009. Since Date1 is not a leap year, the date 29-Feb is not counted. But the same formula with Date1 = 1-Feb-2008 returns 29, since Date1 is a leap year and therefore the date 29-Feb is counted.
---
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39185297
The simple fact that DATEDIF was implemented to provide compatibility for 1-2-3 workbooks, and that the 1-2-3 engineers took many shortcuts in favor of a small footprint despite funky functionality, and that the Excel engineers did their best to simulate that funkiness, should lead one to avoid it if following best practices. Microsoft would prefer that the function just go away but keep supporting it in half-baked ways because not doing so would cause many more problems.

And it's easy to do the math correctly with relatively simple math formulas without using DATEDIF.

Kevin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question