Solved

# Access VBA: Calculate an exact number of months

Posted on 2011-10-04
427 Views
I need to be able to come up with a way to calculate an exact number of months. DateDiff will not work as the dates I'm using could be several years apart. I came up with the following to calculate years:

``````Public Function elapsed_years_function(first_date As Date, Optional second_date As Date = 0) As Integer

'The purpose of this function is to determine the number of WHOLE years elapsed

Dim elapsed_years As Integer

If second_date = 0 Then
'was a date input? if not use the current date.
second_date = Date
End If

elapsed_years = DateDiff("yyyy", first_date, second_date)

If second_date < DateSerial(Year(second_date), Month(first_date), Day(first_date)) Then
elapsed_years = elapsed_years - 1
End If

elapsed_years_function = elapsed_years

End Function
``````

Months is making my head hurt at the moment, though. The other hiccup: The date field I'll be using for the oldest date is input as MM/YY only, so access stores it as MM/01/YYYY. I need to calculate the months based on the LAST day of the month instead of the first. Any ideas?
0
Question by:USArmy1

LVL 61

Expert Comment

Why do you feel that DateDiff wont work for dates that are years apart?

intMos = Datediff("m",#1/1/2001#,#1/1/2010#)
0

LVL 61

Expert Comment

<<I need to calculate the months based on the LAST day of the month instead of the first. Any ideas?>>

You can calculate the last day of the month like this:

dtLastDayOfMonth = cdate(Month(dtYourDate)+ 1 & "/01/" & year(dtYourDate)) -1

And then use that value for any further calculations
0

LVL 47

Expert Comment

Need to understand exactly what you are looking for.

If I understand correctly, you want the number of whole months between two dates, where the first_date is the last day of a particular month.  Will you be passing the MM/YY format to the function, or an actual date in the format MM/DD/YY?

So the number of whole months between 9/30/11 and 10/9/11 = 0

How about between 8/31/11 and 9/30/11 = ??

Since both are the last day of the month, would that qualify as a full month?

0

LVL 1

Author Comment

Okay, you're right. Now I just feel dumb. I was under the assumption that it only looked at the months part of the date, as the datediff year function does. Meh, I digress.

So does it ignore the day then? I suppose if it does, then this serves my exact purpose and this was a worthless question...
0

LVL 47

Expert Comment

Well,

datediff("m", #09/30/11#, #10/1/11#) = 1

If that is what you want, great.  I was assuming that you wanted whole months, which is a bit more problematic, since months have different #'s of days.
0

LVL 1

Author Comment

fyed:

The dates ARE stored as MM/DD/YY. They just aren't displayed to users that way.

In your examples, 1: Yes;       2. 1;       3. Yes      4. I would think 0, just because for my purposes, the Military calculates months based on 30 days, and that would also give a "benefit of the doubt" favorable to the individual.
0

LVL 1

Author Comment

And yes, actually, I knew there was a reason I was thinking the way I was...

I don't want it to show 8 (for example) unless 8 full months have passed.
0

LVL 47

Accepted Solution

I work for the Army, and question the "30 day month" you mention above.

Is this for some sort of training or tool calibration system?

?datediff("m", firstdate, seconddate) + (Format(seconddate, "yymm") = format(seconddate + 1, "yymm"))
0

LVL 1

Author Comment

Chapter 1 of the DOD Financial Management regulation (http://comptroller.defense.gov/fmr/07a/07aarch/07a01.pdf) states that all calculations are based off of a 30 day month.

I'll test some dates with what you provided above and see if it looks right.

Thanks so far!
0

LVL 1

Author Closing Comment

This solution seems to work, so I'll go with it. Thanks.
0

LVL 47

Expert Comment

ah, finance, my least favorite subject.
0

## Featured Post

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…