Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Color Scales Examples in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Data Series in Excel

References

  • How to get last column number in range in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to retrieve first match between two ranges in Excel
  • Lookup entire row in Excel
  • How to get last row in mixed data with blanks in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation require unique number
  • Excel Data validation only dates between
  • Excel Data validation exists in list
  • Prevent invalid data entering in specific cells

How to calculate months between dates in Excel

by

To calculate months between two dates as a whole number, you can use the DATEDIF function.

Formula

=DATEDIF(start_date,end_date,"m")

Explanation

In the example shown, the formula in D6 is:

=DATEDIF(B6,C6,"m")

Note that the DATEDIF automatically rounds down. To round up to the nearest month, see below.

The mystery of DATEDIF

The DATEDIF function is a “compatibility” function that comes from Lotus 1-2-3. For reasons unknown, DATEDIF is only documented in Excel 2000, and will not appear as a suggested function in the formula bar. However, you can use DATEDIF in all Excel versions since that time, you just need to enter the function manually. Excel will not help you with function arguments.

How this formula works

DATEDIF takes 3 arguments: start date, end_date, and unit. In this case, we want months, so we supply “m” for unit.

DATEDIF automatically calculates and returns a number for months, rounded down.

Nearest whole month

If you want to calculate months to the nearest whole month, you can make a simple adjustment to the formula:

=DATEDIF(start_date,end_date+15,"m")

This ensures that end dates occurring in the 2nd half of the month are treated like dates in the following month, effectively rounding up the final result.

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel XOR Function
  • How to use Excel OR Function
  • How to use Excel NOT Function
  • How to use IFS function in Excel

Date Time

  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Get week number from date in Excel
  • How to calculate next day of week in Excel
  • How to calculate working days left in month in Excel
  • Get date from day number in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • How to randomly assign people to groups in Excel
  • Map text to numbers in Excel
  • Group arbitrary text values in Excel
  • Group times into unequal buckets in Excel

General

  • 3D SUMIF for multiple worksheets in Excel
  • Share Excel data with Word documents
  • 231 Keyboard Shortcut Keys In Excel
  • How to calculate project complete percentage in Excel
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning