Calculate Full Months Between Dates in Access Queries

In this Microsoft Access tutorial, I will show you how to calculate the number of whole months between two dates, a common challenge when using DateDiff. We'll address the issue where DateDiff counts partial months as complete by incorporating a technique that considers day values to ensure accuracy. You'll learn to implement this calculation in a query in Part 1. For those interested in advancing their skills, I'll demonstrate how to create a function for more streamlined usage in Part 2.

Megan from Pasadena, California (a Platinum Member) asks: How do I find out how many full months are between two dates in Microsoft Access? I tried using DateDiff, but it seems to just count the months without considering the exact days. For example, going from February 12 to March 12 gives me one month, which is fine, but February 12 to March 2 also counts as one month for some reason. I need a way to only count whole months, factoring in the days too. Could you show me how to make it work properly?

Prerequisites

• DateDiff
• IIF Function
• Year Month Day
• Calculated Fields

Links

• ISO Date Format
• Code Vault - Whole Months - Gold Members

Recommended Courses

• More with Date Functions
• Advanced DateTime Seminar

Up Next

• Whole Months, Part 2

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Diamond Sponsors - Information on our Sponsors Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.

Keywords

TechHelp Access, calculate whole months between dates, DateDiff with month setting, subtract one for whole months, function for whole months calculation, non-VBA query method, evaluate day values, IIf function in queries, mistakes with DateDiff arguments, fix DateDiff errors, ISO date format, date subtraction logic, free template demonstration, start date and end date tables, calculated query fields