Solved

# How to find the exact difference in months between two dates in Access

Posted on 2011-02-14

I have two sets of dates I am trying to find out the exact number of months between them. I have tried two different formulas with different results for each formula. I can't seem to get my head around the year part. I am currently using these formulas in a unbound text box to calculate when a form is opened.

Current Formula is

=IIf(DatePart("d",[CurrentISPDate])>DatePart("d",[PreviousISP]),DateDiff("m",[PreviousISP],[CurrentISPDate])+1,DateDiff("m",[PreviousISP],[CurrentISPDate])-1)

Old Formula is

=DateDiff("m",[PreviousISP],[CurrentISPDate])

With the following two dates here are the results:

Current Formula

3/10/2010 - 9/2/10 - 5 Months

06/25/10 - 1/12/11 - 6 Months

Old Formula

3/10/2010 - 9/2/10 - 6 Months

06/25/10 - 1/12/11 - 7 Months

The current formula works for the exact months as long as the dates are within the same year. Different years the current formula defaults to just months.

What am I missing that will accurately calculate the difference in months between the dates? I know it is something simple.