• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1779
  • Last Modified:

How to calculate number of months between 2 dates

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
Vendettta
Asked:
Vendettta
1 Solution
 
Martin LissOlder than dirtCommented:
Here's a Microsoft support article.
0
 
albeloCommented:
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
 
barry houdiniCommented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
VendetttaAuthor Commented:
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
 
albeloCommented:
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
 
albeloCommented:
Make sure you format the cell with the formula as General or Number
0
 
barry houdiniCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now