How To Calculate the Number of Months Between Two Dates in Excel
Mar 13, 2025
Do you need to calculate the number of months between two dates in Excel?
You may need to perform this task when tracking project timelines, calculating loan durations, or analyzing trends. Fortunately, Microsoft Excel provides several ways to get the job done. This Excel tutorial covers multiple formulas and techniques to calculate months between dates.
Using the DATEDIF Function (Best for Whole Months)
You should opt for the DATEDIF function when only interested in the number of months between two dates. The function counts only complete months and ignores any remaining days that don’t form a full month.
Formula: =DATEDIF(start_date, end_date, "m")
Where:
-
start date: The earlier date.
-
end date: The later date.
-
"m": The unit for full months.
Example.
Let's assume you want to calculate the number of months between January 15, 2023, and August 20, 2023. Use the formula above and replace start date and end date with appropriate cell references.
Formula: =DATEDIF(A1, B1, "m")
This means 7 full months have passed between the two dates.
Handling errors when using the DATEDIF function.
If the start date is later than the end date, DATEDIF returns a #NUM! error. To fix the issue, use IFERROR to handle negative values.
Example formula: =IFERROR(DATEDIF(A1, B1, "m"), "Invalid date range")
Using the YEARFRAC Function (Best for Fractional Months)
The YEARFRAC function calculates the exact proportion of time between two dates, including partial months. Unlike the DATEDIF formula, which only counts whole months, YEARFRAC returns a decimal value representing the fraction of a year between two dates. Multiplying the year value by 12 converts it into months, including partial months.
Formula: =YEARFRAC(start_date, end_date) * 12
Where:
-
YEARFRAC(start_date, end_date): Returns the number of years between two dates.
-
Multiplying by 12 converts years to months.
Example.
Let's calculate the number of complete and partial months between 15/01/2023 and 20/08/2023.
This means the time difference includes 7 full months and 17% of an additional month.
Read more: How to Change Date Format in Excel.
Calculate Months and Days Between Two Dates
What if you want to know the actual number of months and days between two dates? Don't worry, you can do that too. All you need is an Excel formula that calculates the full months between the two dates and the remaining days.
Formula: =DATEDIF(start_date, end_date, "m") & " months and " & DATEDIF(start_date, end_date, "md") & " days"
Where:
-
DATEDIF(start_date, end_date, "m"): This returns the full months between the two dates.
-
DATEDIF(start_date, end_date, "md"): This returns the remaining days after calculating the full months.
-
"md" specifically calculates the days excluding the months (i.e., only the days between the current month’s date and the start date).
-
Example.
Let's calculate both the months and days between 15/01/2023 and 20/08/2023.
This formula tells you there are 7 full months and 5 extra days between January 15, 2023 and August 20, 2023.
Rounding Up When Counting Months Between Two Dates
When planning or evaluating a project, you may need to count the number of months in its duration. In such a situation, a formula based on the YEARFRAC function can help you count any partial month as a full month.
Formula: =ROUNDUP(YEARFRAC(A1, B1) * 12, 0)
where:
-
YEARFRAC(A1, B1): Calculates the fraction of a year between two dates.
-
YEARFRAC(A1, B1) * 12: Converts this fraction into months (0.599 * 12 = 7.19).
-
ROUNDUP(YEARFRAC(A1, B1) * 12, 0): Rounds up the result (7.19 → 8), which means any extra days count as a full month.
Example.
Read more: How to Add Month to Date in Excel.
Rounding to the Nearest Month When Counting Months Between Two Dates
You can choose to round to the nearest whole month instead of rounding down (using DATEDIF) or rounding up (using ROUNDUP). The YEARFRAC method is perfect for this since it treats partial months as fractions.
Formula: =ROUND(YEARFRAC(A1, B1) * 12, 0)
Where:
-
YEARFRAC(A1, B1): Calculates the exact fraction of a year between two dates.
-
YEARFRAC(A1, B1) * 12: Converts the fraction into months.
-
ROUND(YEARFRAC(A1, B1) * 12, 0): Rounds the number of months to the nearest whole number.
If YEARFRAC(A1, B1) * 12 = 7.5, this formula returns 8.
Final Thoughts
You can easily calculate the number of months between two dates in Excel with functions like DATEDIF, YEARFRAC, and rounding techniques. Excel provides flexible solutions for various scenarios, whether you need whole months, fractional months, or precise month-and-day calculations. Choose the most suitable option from our guide!
For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.
Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!
FAQ
1. How does the DATEDIF formula work?
The DATEDIF formula subtracts two dates in years ("y"), months ("m"), or days ("d") and ignores incomplete units. For example, =DATEDIF(A1, B1, "m") returns only the full months between the start and end date, excluding extra days.
2. Can I count months including partial months in Excel?
Yes, use the YEARFRAC function multiplied by 12 to get fractional months. If you need to round up or to the nearest whole month, use ROUNDUP or ROUND functions.
3. Which formula is best for calculating loan durations in months?
For precise loan duration, use YEARFRAC(start_date, end_date) * 12 to include partial months. If your loan terms require full months, the DATEDIF function with "m" is the best choice.
Related Articles
How to Make a Spreadsheet Live
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
