Months between two dates?

[meinhunna]

how can i find number of months between two dates (inclusive)

for eg

01/01/2004 and 20/12/2004 ==> 12

i have tried using DateDiff function but it results in 11

thanks

 

[koala15]

So are you intending to ignore the day part of the date completely?

For example, should 15/1/04 to 13/12/04 result in 12?

"Life is full of learning, and then there is wisdom"

 

[meinhunna]

true, i have to ignore day part.

 

[johnwm]

Just use the Month() function:

Month("15/1/04") will return 1
Month("13/12/04") will return 12

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[AEtherson]

DateDiff("M", dateadd("m",-1,"15/1/04"), "13/12/04")

 

[koala15]

The challenge with the Month() function is that if the dates are in different years, then the logic may have to compensate for that fact.

However AEtherson's solution has that fact built in.

"Life is full of learning, and then there is wisdom"

 

[strongm]

Of course, if we consider 31 Jan 2005 and 1 Feb 2005, most of these solutions run in to problems.

A keyword search in this forum will find a (quite large)number of previous threads on this subject, many of which go into the details of where you can be caught out. Probably worth a search and a read.

 

[CubeE101]

What if you break it down by Month Day And Year ... and calculate it like this...

Date1 = "01/01/2004"
Date2 = "21/12/2005"
M = Split(Date1,"/")(1) - Split(Date2,"/")(1)
D = Split(Date1,"/")(0) - Split(Date2,"/")(0)
Y = Split(Date1,"/")(2) - Split(Date2,"/")(2)
Y = Y * 12
D = D / Abs(D)
M = Abs(M + D + Y)

Any reason that would not work?

Supporting Data, and expanation...

  01  01  2004
 -21 -12 -2004
--------------
 -20 -11  0000

Or

  21  12  2004
 -01 -01 -2004
--------------
  20  11  0000

31 Jan 2005 and 1 Feb 2005

  31  01  2005
 -01 -02 -2005
--------------
  30 -01  0000

Or

  01  02  2005
 -31 -01 -2005
--------------
 -30  01  0000

**************

Date1 = "31/01/2005"
Date2 = "01/02/2005"
M = Split(Date1,"/")(1) - Split(Date2,"/")(1)   '= -1
D = Split(Date1,"/")(0) - Split(Date2,"/")(0)   '= 30
Y = Split(Date1,"/")(2) - Split(Date2,"/")(2)   '= 2004 - 2004 ... 0
Y = Y * 12                                      '= 0 * 12 ... 0
D = D / Abs(D)                                  '= 30 / 30 ... 1
M = Abs(M + D + Y)                              '= Abs(-1 + 1 + 0) ... 0

-----Or-------

Date1 = "01/01/2004"
Date2 = "21/12/2004"
M = Split(Date1,"/")(1) - Split(Date2,"/")(1)   '= 01 - 12 ... -11
D = Split(Date1,"/")(0) - Split(Date2,"/")(0)   '= 01 - 21 ... -20
Y = Split(Date1,"/")(2) - Split(Date2,"/")(2)   '= 2004 - 2004 ... 0
Y = Y * 12                                      '= 0 * 12 ... 0
D = D / Abs(D)                                  '= -20 / 20 ... -1
M = Abs(M + D + Y)                              '= Abs(-11 + -1 + 0) ... 12

-----Or-------

Date1 = "01/01/2004"
Date2 = "21/12/2005"
M = Split(Date1,"/")(1) - Split(Date2,"/")(1)   '= 01 - 12 ... -11
D = Split(Date1,"/")(0) - Split(Date2,"/")(0)   '= 01 - 21 ... -20
Y = Split(Date1,"/")(2) - Split(Date2,"/")(2)   '= 2004 - 2005 ... -1
Y = Y * 12                                      '= -1 * 12 ... -12
D = D / Abs(D)                                  '= -20 / 20 ... -1
M = Abs(M + D + Y)                              '= Abs(-11 + -1 + -12) ... 24

Is there an instance where this would not work?

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.