Count Whole Months (1 Viewer)

[Matty]

Hi,

I have a question about counting the number of months between two dates. The user enters a Start Date and Expiry Date, and I need to make sure those dates are exactly whole months. For example January 1, 2005 to March 31, 2005 gives me exactly 3 months. But January 1, 2005 to April 1, 2005 will give me 3 months plus a day.

I tried to do a DateDiff on that second date range, but it gives me 4. Is there some way for me to get a result of 3 months and 1 day? Or at least allow me to count the number of whole months? If I get the number of whole months, I can do some math to figure out the extra days.

Thanks in advance,

-Matt

 

[Jon K]

Take a look at this thread. You may find the code in the getTimeElapsed() function helpful.
http://www.access-programmers.co.uk/forums/showthread.php?t=67472

.

 

[Matty]

Thanks for the link. While I was waiting for a reply, the code to calculate the whole months came to me. Here's what it turned out as:

Public Function intWholeMonths(datStartDate As Date, datEndDate As Date) As Integer
On Error GoTo Err_intWholeMonths

    Dim intMonths As Integer
    Dim datDate As Date
    Dim blnPast As Boolean
    
    intMonths = 1
    blnPast = False
    
    Do Until blnPast = True
        datDate = DateAdd("m", intMonths, datStartDate)
        If datDate > datEndDate Then
            blnPast = True
        Else
            intMonths = intMonths + 1
        End If
    Loop
    
    intWholeMonths = intMonths - 1

Exit_intWholeMonths:
    Exit Function

Err_intWholeMonths:
'Add record to log
    Call intAddLogRecord("intWholeMonths function -- " & Err.Description)
    Resume Exit_intWholeMonths
End Function

Here's how it works: I keep adding one month to the start date, keeping track of how many months I'm adding. Once my month addition passes the end date, I subtract one from the months counter and that gives me the total whole months before the end date.

So from if my dates are January 1, 2005 and April 1, 2005, the months in datDate will be January 31, February 28, March 31 and April 30. Since April 30 is past April 1, it uses March 31 is the last point.

The intAddLogRecord just adds a record to my error log table.

Thanks again for the link -- I'm sure I'll be using some of those date functions in the future. Right now I just needed a simple "whole months" calculation to get my program working.

 

[Jon K]

In the first post you wrote:
.... January 1, 2005 to April 1, 2005 .... Is there some way for me to get a result of 3 months and 1 day?


This modified getTimeElapsed() function should give you the result of "3 months and 1 day" as a string:

Public Function getTimeElapsed(StartDate As Date, EndDate As Date) As String
   Dim MM As Integer
   Dim DD As Long
  
   EndDate = EndDate + 1
    
   MM = DateDiff("m", StartDate, EndDate)
   DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
  
   If DD < 0 Then
      MM = MM - 1
      DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
   End If
  
   getTimeElapsed = MM & IIf(MM = 1, " month ", " months ") & _
                   IIf(DD = 0, "", DD & IIf(DD = 1, " day", " days"))

End Function

If what you needed is the number of whole months as an integer, then the MM in the function is what you needed.
.

 

[Matty]

I've got most of the function figured out, but when would DD be less than zero?

 

[EMP]

DD would be negative if DateAdd("m", MM, StartDate) > EndDate

When this happens, DD needs to be calculated again by deducting 1 month from MM.

,

 

[Matty]

Okay, that makes sense. Here's how I modified that function to work the way I want it to:

Public Function intTimeElapsed(datStartDate As Date, datEndDate As Date, strData) As Integer
On Error GoTo Err_intTimeElapsed

    Dim intMonths As Integer
    Dim intDays As Integer
    
    datEndDate = DateAdd("d", 1, datEndDate)
    
'Find the number of months between the passed start and end date
    intMonths = DateDiff("m", datStartDate, datEndDate)
    
'Find the number of extra days by adding the number in intMonths to the start date and finding the number of days between that date
'   and the end date
    intDays = DateDiff("d", DateAdd("m", intMonths, datStartDate), datEndDate)
    
    If intDays < 0 Then
        intMonths = intMonths - 1
        intDays = DateDiff("d", DateAdd("m", intMonths, datStartDate), datEndDate)
    End If
    
    Select Case strData
        Case "Months"
            intTimeElapsed = intMonths
        Case "Days"
            intTimeElapsed = intDays
        Case Else
            intTimeElapsed = 0
    End Select
                   
Exit_intTimeElapsed:
    Exit Function

Err_intTimeElapsed:
'Add record to log
    Call intAddLogRecord("intTimeElapsed function -- " & Err.Description)
    Resume Exit_intTimeElapsed
End Function

I wanted the function to return an integer, so I pass a string specifying what type of data I want (months or days).