Access VBA: Calculate an exact number of months

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

Open in new window


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?
LVL 1
USArmy1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

intMos = Datediff("m",#1/1/2001#,#1/1/2010#)
0
mbizupCommented:
<<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
Dale FyeCommented:
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?

How about: 1/31/12 and 2/28/12

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

USArmy1Author Commented:
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
Dale FyeCommented:
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
USArmy1Author Commented:
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
USArmy1Author Commented:
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
Dale FyeCommented:
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?

How about something like:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
USArmy1Author Commented:
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
USArmy1Author Commented:
This solution seems to work, so I'll go with it. Thanks.
0
Dale FyeCommented:
ah, finance, my least favorite subject.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.