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:
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?
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 FunctionMonths 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?
<<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
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
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
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
ASKER
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...
So does it ignore the day then? I suppose if it does, then this serves my exact purpose and this was a worthless question...
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.
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.
ASKER
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.
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.
ASKER
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.
I don't want it to show 8 (for example) unless 8 full months have passed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
I'll test some dates with what you provided above and see if it looks right.
Thanks so far!
ASKER
This solution seems to work, so I'll go with it. Thanks.
ah, finance, my least favorite subject.
intMos = Datediff("m",#1/1/2001#,#1