Function needed for calculating elasped months in VBScript

Posted on 2004-12-01
Last Modified: 2010-04-06
In order to track employee Paid Time Off (PTO) I need an accurate way in vbscript to calculate the number of months elasped between Date1 and Date2.  vbscript's DateDiff() is inaccurate and yields rounding errors.  For example 6/15/2003 -> 6/1/2004 should result in "11", not 12, 6/15/2003 -> 9/1/2003 should give 2 (not 3) and 6/15/2004 -> 6/14/2004 should yield 12.  Has anyone written an accurate function to do this or must I revert to a more powerful language that provides true date manipulations?  I can't be the first person to need this, but I see nothing in the archives.  Thanks in advance.
Question by:bmoore7624
    LVL 25

    Expert Comment

    Dim dtNow, dtThen
    Dim Elapsed
        dtNow = "12:30:00"
        dtThen = "09:00:00"
        Elapsed = DateDiff("n", dtThen, dtNow) / 60
        MsgBox Elapsed
    LVL 5

    Expert Comment

    May be this helps:
    From the comments somebody obviously hit the sam problem as you but with years.

    LVL 12

    Expert Comment

    OK...try this out...

    date1 = "6/15/2003"
    date2 = "6/1/2004"

    intdaydiff = DateDiff("d",date1,date2)
    varmonths = Split(CStr(intdaydiff / 30),".")

    Response.Write varmonths(0)

    Author Comment

    This last one (by AshleyBryant) is close to what I'm using now:
        CInt( (DateDiff("s",StartDate,EndDate)/ ( 60 * 60 * 24 * 30) ))
    and yeilds similar results (and problems).  Both assume uniform 30 day months, which not accurate.  I need to know that if Date1=2/15/2003 and Date2=3/15/2003 that's a calendar month, even though it is only 28 days.  Likewise 3/5 - 4/4 shouldn't be a calendar month, even though it is 30 days.  That's how payroll works.  I guess I'm looking for calendar months.
    LVL 58

    Accepted Solution

    Try this function ...

    function GetDateDiffInMonths(Date1, Date2)
        Dim Months, Date3

        Months = DateDiff("m", Date1, Date2)
        Date3 = DateAdd("m", Months, Date1)
        Date3 = DateAdd("d", -1, Date3)

        If (DateDiff("d", Date2, Date3) > 0) Then
            Months = Months - 1
        End If

        GetDateDiffInMonths = Months
    end function

    Date1 = "6/15/2003"
    Date2 = "6/1/2004"

    Date1 = "6/15/2003"
    Date2 = "9/1/2003"

    Date1 = "6/15/2003"
    Date2 = "6/14/2004"

    Date1 = "2/15/2003"
    Date2 = "3/15/2003"

    Months = GetDateDiffInMonths(Date1, Date2)

    Call MsgBox(Months)

    LVL 12

    Assisted Solution

    Function CalcPTO(StartDate,FinishDate)
          BaseMonth = DateDiff("m",StartDate,FinishDate)
          If Day(StartDate) > Day(FinishDate) then BaseMonth = BaseMonth - 1
          CalcPTO = BaseMonth
    End Function

    Date1 = "2/15/2003"
    Date2 = "3/15/2003"

    ActualPTO = CalcPTO(Date1,Date2)

    Response.Write ActualPTO

    The only scenario that you posted that this does not account for is getting 12 months between 6/15/2003 and 6/14/2004.  That example you gave didn't seem to fit in with the 3/5 - 4/4 not turning out to be a calendar month.  So, either there was a typo or there are certain exceptions to the rules we've been given so far.

    Author Comment

    Thanks very much amit_g & AshleyBryant.  Both solutions are elegant and work all the way.  I accepted amit_g's answer since it came in first.  Much appreciated.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    When you work with shopping cart / ecommerce relates web sites, you need to pass the certain form post details to the payment gateway process page with required details for the products items you give to order. Also you may need to track the ordered…
    There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
    Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
    The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now