Calculating Month Difference . Urgent

Something like this ;

Public Function testing()
Dim strDate As String, strPeriod As String

strDate = CDate("1-2-00") ' Date from db

strPeriod = Month(Date) - Month(strDate)

MsgBox strPeriod ' Months elapesed in period

End Function

use datediff will be much easier.

Dim m
Dim date1
Dim date2

date1 = "23/11/1999"
date2 = "23/04/2001"

m = DateDiff("m", date1, date2)

Select Case m

Case 1
    MsgBox m
Case 2
    MsgBox m
Case 3
    MsgBox m
Case Else
    MsgBox m
   
End Select

good luck.

Hi sumantachandra,

DateDiff is definitely the way to go however in order to fully satisfy your needs:

Private Function MonthsBetween(ByVal Registration As Date, Optional ByVal Current As Date) As Integer
    Dim intMonths As Integer
    Dim intDays As Integer
    Dim dteSwap As Date
    If Current = vbEmpty Then Current = Now() 'Allows for not passing the second date
    If Current < Registration Then 'Handle the dates being given in the wrong order by swapping them round
        dteSwap = Current
        Current = Registration
        Registration = dteSwap
    End If
    intMonths = DateDiff("m", Registration, Current) 'Determines the number of whole months
    intDays = Day(Current) - Day(Registration) 'Determines whether day of second month is higher than day of first month, this means that an additional month needs to be added
    intMonths = intMonths + IIf(intDays >= 1, 1, 0) 'Add the extra month if necessary
    MonthsBetween = intMonths
End Function


Call it like:

    MsgBox MonthsBetween(CDate("2001-03-23"),CDate("2001-04-24"))

Dim monthDif as integer
monthDif = DateDiff("m",yourdate,Now)
MsgBox monthDif

Regards,
CJ

has been said already, sorry :-/

has been said already, sorry :-/

Hi,

This works too:

Private Sub Command1_Click()
Print TrueMonthDiff("23/3/1", "23/4/1")
Print TrueMonthDiff("23/3/1", "24/4/1")
Print TrueMonthDiff("23/11/99", "23/4/1")
End Sub

Private Function TrueMonthDiff(date1 As String, date2 As String)
Dim date3 As String
TrueMonthDiff = DateDiff("m", date1, date2)
date3 = DateAdd("m", TrueMonthDiff, date1)
If DateDiff("d", date2, date3) < 0 Then
TrueMonthDiff = TrueMonthDiff + 1
End If
End Function

A quick note about dates: I've found it useful in the past to never pass dates around as string (always use a date datatype). Convert user input to a date at the earliest possible opportunity. The implicit date conversion of VB can get a little confused with various international date settings.
When using a date constant, use #mm/dd/yyyy# rather than a string. I've also found it saves stress if you write your own string->date function which uses a single format, so you are never dependent upon the user's international settings.

sumantachandra: Given that this was "urgent" a more speedy acceptance of an answer/comment could be considered appropriate!

sumantachandra, I guess your internet connection might be having some problems. all of us here are still waiting for your response. Pls let us know if our suggestion isn't your solution. I'm sure many of us here are more than willing to help u. Pls dun just leave us like that without attending to your opened question.

thanks & cheers!

Hi sumantachandra,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days.  I will suggest to:

    Split points between: ianouii and TimCottee and deighton

*** this look a bit like schoolwork, but several worthwhile responses!

sumantachandra, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will followup.

EXPERTS: Please post closing recommendations.
==========
DanRollins -- EE database cleanup volunteer

thanks.

Per recommendation, force-accepted.

Netminder
CS Moderator

ianouii: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20317591
TimCottee: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20317611