?
Solved

Function needed for calculating elasped months in VBScript

Posted on 2004-12-01
7
Medium Priority
?
256 Views
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.
0
Comment
Question by:bmoore7624
7 Comments
 
LVL 25

Expert Comment

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

Expert Comment

by:SMartinHamburg
ID: 12720496
May be this helps:
http://www.bitbucketheaven.com/code/age.shtml
From the comments somebody obviously hit the sam problem as you but with years.

Regards
0
 
LVL 12

Expert Comment

by:Ashley Bryant
ID: 12720820
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)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:bmoore7624
ID: 12721953
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.
0
 
LVL 58

Accepted Solution

by:
amit_g earned 1000 total points
ID: 12723541
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)

0
 
LVL 12

Assisted Solution

by:Ashley Bryant
Ashley Bryant earned 400 total points
ID: 12726708
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.
0
 

Author Comment

by:bmoore7624
ID: 12730016
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.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

840 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