Solved

How to calculate number of months between 2 dates

Posted on 2013-01-16
7
1,722 Views
Last Modified: 2013-01-24
I am trying to calculate the number of months between two dates.  The first date is the initial contact and the second date is the current date.  The current date will automatically update with today's date.  Then I will take that number and multiply it against a dollar amount.

Thanks in advance.
0
Comment
Question by:Vendettta
7 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38785000
Here's a Microsoft support article.
0
 
LVL 5

Expert Comment

by:albelo
ID: 38785004
Assume you have the following in Excel

Cell A1: 6/7/2007
Cell A2: 6/9/2009

Fomula: =(YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1)
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38785156
DATEDIF counts whole months only, e.g.

=DATEDIF(A2,B2,"m")

or do you want fractions or rounding?

To round the nearest whole number of months

=DATEDIF(A2,B2+15,"m")

regards, barry
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Vendettta
ID: 38789677
The second date is going to be based off the system date.  Which will change automatically.  Each formula assumes a set date to be entered.  It needs to be tied to the system date.


I look forward to the update.
0
 
LVL 5

Expert Comment

by:albelo
ID: 38789707
IF you used =today() in Cell A2 it would return system date and formula would remain the
same.

or

=(YEAR(TODAY())-YEAR(A1))*12+MONTH(TODAY())-MONTH(A1)
0
 
LVL 5

Expert Comment

by:albelo
ID: 38789713
Make sure you format the cell with the formula as General or Number
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 38790042
The same applies with my DATEDIF suggestion - this formula will return the number of whole months between a date in A2 and today's date

=DATEDIF(A2,TODAY(),"m")

so if A2 = 1/1/2013 then that will give you 0 because no full months have yet passed......but if you use my other suggestion, i.e.

=DATEDIF(A2,TODAY()+15,"m")

That will round to the nearest number of months - for my example 1

...or do you want a fractional number? If you do then try YEARFRAC like this

=YEARFRAC(A2,TODAY())*12

In all of those cases you can simply multiply by your dollar amount within the same formula if you like, e.g. with dollar amount in B2 that last one becomes.

=YEARFRAC(A2,TODAY())*12*B2

If none of those work for you perhaps consider posting a few representative examples - e.g. which dates you have and what results you require for those dates.

regards, barry
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now