• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

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?
0
USArmy1
Asked:
USArmy1
  • 5
  • 4
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now