424,968 Members | 1,589 Online 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
3 Replies

 P: n/a 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 monthsbetween two dates. DateDiff won't work because I need to add one monthif 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 0everytime. 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) AsInteger 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 monthsbetween two dates. DateDiff won't work because I need to add one monthif 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 0everytime. 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 thebeginning. Once there, use F8 to step through the routine. Hoverover the variables to see their assigned values at any moment.By the time you have walked through it once you will see what theproblem is.mike************************************Public Function CalculateMonths(ipdtStart As Date, ipdtEnd As Date) AsInteger 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 