By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,589 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

# of Months Calcuation only returns 0

P: n/a
I'm trying to create a function that calculates the number of months
between two dates. DateDiff won't work because I need to add one month
if the day of the end_date is greater than the day of the start_date.
I can't determine where the problem is in the code below. It returns 0
everytime. Any input is welcomed.
************************************

Public Function CalculateMonths(ipdtStart As Date, ipdtEnd As Date) As
Integer

Dim intNumberOfMonths As Integer
Dim intStartMonth As Integer
Dim intEndMonth As Integer
Dim intYears As Integer
intStartMonth = Month(Start_Date)
intEndMonth = Month(End_Date)
intYears = Year(End_Date) - Year(Start_Date)
If Day(ipdtStart) >= Day(ipdtEnd) Then
intNumberOfMonths = (intEndMonth - intStartMonth)
Else
intNumberOfMonths = (intEndMonth - intStartMonth) + 1

End If

CalculateMonths = intNumberOfMonths + (12 * intYears)

End Function

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

<lp*******@aipt.org> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I'm trying to create a function that calculates the number of months
between two dates. DateDiff won't work because I need to add one month
if the day of the end_date is greater than the day of the start_date.
I can't determine where the problem is in the code below. It returns 0
everytime. Any input is welcomed.
************************************

Public Function CalculateMonths(ipdtStart As Date, ipdtEnd As Date) As
Integer

Dim intNumberOfMonths As Integer
Dim intStartMonth As Integer
Dim intEndMonth As Integer
Dim intYears As Integer
intStartMonth = Month(Start_Date)
intEndMonth = Month(End_Date)
intYears = Year(End_Date) - Year(Start_Date)
If Day(ipdtStart) >= Day(ipdtEnd) Then
intNumberOfMonths = (intEndMonth - intStartMonth)
Else
intNumberOfMonths = (intEndMonth - intStartMonth) + 1

End If

CalculateMonths = intNumberOfMonths + (12 * intYears)

End Function


How about something like:

Public Function CalculateMonths(ipdtStart As Date, ipdtEnd As Date) As
Integer

CalculateMonths = DateDiff("m", ipdtEnd, ipdtStart) _
- (DatePart("d", ipdtEnd) >= DatePart("d",
ipdtStart))

End Function
Nov 13 '05 #2

P: n/a
On 13 Jun 2005 08:15:20 -0700, lp*******@aipt.org wrote:
I'm trying to create a function that calculates the number of months
between two dates. DateDiff won't work because I need to add one month
if the day of the end_date is greater than the day of the start_date.
I can't determine where the problem is in the code below. It returns 0
everytime. Any input is welcomed.
What about an IFF? In general terms:

DateDiff + iff(Day(end_date)>Day(start_date),1,0)

Otherwise, to debug the following, put a STOP command at the
beginning. Once there, use F8 to step through the routine. Hover
over the variables to see their assigned values at any moment.

By the time you have walked through it once you will see what the
problem is.

mike
************************************

Public Function CalculateMonths(ipdtStart As Date, ipdtEnd As Date) As
Integer

Dim intNumberOfMonths As Integer
Dim intStartMonth As Integer
Dim intEndMonth As Integer
Dim intYears As Integer
intStartMonth = Month(Start_Date)
intEndMonth = Month(End_Date)
intYears = Year(End_Date) - Year(Start_Date)
If Day(ipdtStart) >= Day(ipdtEnd) Then
intNumberOfMonths = (intEndMonth - intStartMonth)
Else
intNumberOfMonths = (intEndMonth - intStartMonth) + 1

End If

CalculateMonths = intNumberOfMonths + (12 * intYears)

End Function


Nov 13 '05 #3

P: n/a
On Mon, 13 Jun 2005 16:10:21 GMT, mb******@pacbell.net.invalid (Mike
Preston) wrote:
On 13 Jun 2005 08:15:20 -0700, lp*******@aipt.org wrote:
I'm trying to create a function that calculates the number of months
between two dates. DateDiff won't work because I need to add one month
if the day of the end_date is greater than the day of the start_date.
I can't determine where the problem is in the code below. It returns 0
everytime. Any input is welcomed.


What about an IFF? In general terms:

DateDiff + iff(Day(end_date)>Day(start_date),1,0)

Otherwise, to debug the following, put a STOP command at the
beginning. Once there, use F8 to step through the routine. Hover
over the variables to see their assigned values at any moment.

By the time you have walked through it once you will see what the
problem is.

mike
************************************

Public Function CalculateMonths(ipdtStart As Date, ipdtEnd As Date) As
Integer

Dim intNumberOfMonths As Integer
Dim intStartMonth As Integer
Dim intEndMonth As Integer
Dim intYears As Integer
intStartMonth = Month(Start_Date)
intEndMonth = Month(End_Date)
intYears = Year(End_Date) - Year(Start_Date)
But it probably has something to do with the fact that the three lines
above reference "Start_Date" and "End_Date" when those variables
aren't anywhere to be found. You probably meant:

intStartMonth = Month(ipdtStart)
intEndMonth = Month(ipdtEnd)
intYears = Year(ipdtEnd) - Year(ipdtStart)

Although I didn't check the logic to see if it actually calculates the
interval you are looking for it to calculate.

mike

If Day(ipdtStart) >= Day(ipdtEnd) Then
intNumberOfMonths = (intEndMonth - intStartMonth)
Else
intNumberOfMonths = (intEndMonth - intStartMonth) + 1

End If

CalculateMonths = intNumberOfMonths + (12 * intYears)

End Function


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.