calculate months between two dates

  • Thread starter Thread starter tracktraining
  • Start date Start date
T

tracktraining

Hi,

I would like to get the amount of months difference between two dates. I
used datediff but that would only give me the months as a whole number. Is
there a way to get a decimal number?

i.e. date1 = 04/01/09; date2 = 06/30/09

datediff("m", date1, date2) = 2 months

but I would like the answer to be 2.9 something.... is this possible?

- Thanks!
 
Maybe something like this:
'Dates are entered cells A2 and B2.
Sub dk()
x = DateDiff("d", Range("A2"), Range("B2")) / 30
MsgBox FormatNumber(x, 2)
End Sub
 
tracktraining said:
i.e. date1 = 04/01/09; date2 = 06/30/09
[....]
but I would like the answer to be 2.9 something.... is this possible?
Click to expand...

Anything is possible. The devil is in the details.

First, I presume that date1 and date2 are type String or Variant, and you
really wrote:

date1 = "04/01/09"
date2 = "06/30/09"

Alternatively, date1 and date2 are abstraction of cell references, e.g.
Range("A1") and Range("A2").

In either case, the number of days is:

days = CDate(date2) - CDate(date1)

Now comes the hard part: how many days in a month?

There is no right answer. You might consider 30, 365/12, 366/12 in leap
years, or 1461/48. Suppose you choose:

dpm = 1461 / 48

Then:

months = days / dpm


----- original message -----
 
The key question here is how many days are in a month? Do you want to
use 30 for all months? Or use the number of days in either the first
or the last month? Also, do you want to include the starting date and
ending date? E.g, how many days are between 1-Jan and 2-Jan? Either 1
or 2 is a correct answer (and I could make the case for 0 if I had
to).

Dim Date1 As Date
Dim Date2 As Date
Dim DaysInMonth As Long
Dim DiffMonths As Double

Date1 = DateSerial(2009, 1, 1)
Date2 = DateSerial(2009, 3, 15)

' 30 days per month...
DaysInMonth = 30
' OR
' month has same number of days as Date1 month...
DaysInMonth = Day(DateSerial(Year(Date1), Month(Date1) + 1, 0))
' OR
' month has same number of days as Date2 month...
DaysInMonth = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0))

DiffMonths = (Date2 - Date1) / DaysInMonth
Debug.Print DiffMonths


This code shows various ways of using different values for the number
of days in a month.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Back
Top