Solved

# Function needed for calculating elasped months in VBScript

Posted on 2004-12-01
Medium Priority
256 Views
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
Question by:bmoore7624

LVL 25

Expert Comment

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

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

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

Author Comment

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

amit_g earned 1000 total points
ID: 12723541
Try this function ...

function GetDateDiffInMonths(Date1, Date2)
Dim Months, Date3

Months = DateDiff("m", Date1, Date2)

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

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

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

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
Course of the Month14 days, 23 hours left to enroll