Function Problems

How to Calculate Months Between Two Month Names in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to calculate the number of elapsed months between a starting birth month and the current month in Excel, treating the start month as zero.

Product: Excel
Device & OS: not provided
Scenario: Calculating elapsed months between two cells where one is a text string of a month name and the other is a date formatted to display the month, ensuring accurate results even if the current month is earlier in the calendar year.
Observed behavior: Standard formulas fail to calculate the correct interval because the cells use mixed data types (text vs. date formatting) and do not account for year wrap-around automatically.

Before you start

Ensure your month names are entered as valid text strings (like 'Jan' or 'January') or correctly formatted dates, as inconsistent data types will cause formula errors.

Solution 1Recommended

Use LET, DATEVALUE, and DATEDIF functions

This method converts both month values into a standardized date format within the same dummy year, adjusting for year wrap-around before calculating the difference.

To properly calculate the difference, Excel needs to evaluate both month names as actual dates. By converting the text string and the TODAY() function into a uniform format, we can safely apply the DATEDIF function.

Identify your reference cells

Assume cell E2 contains the text of the birth month (e.g., 'Jan') and B3 contains the current date using the =TODAY() function.

Input the LET formula

Select the cell where you want the result to appear and enter the following formula: =LET(date1,DATEVALUE("1-"&E2&"-2000"),date2,DATEVALUE("1-"&TEXT(B3,"mmm")&"-2000"),date3,EDATE(date2,12*(date2<date1)),DATEDIF(date1,date3,"m"))

Execute the calculation

Press Enter. Excel will process the dates, apply a 12-month correction if the current month is earlier in the year than the birth month, and return the elapsed months.

Understanding the formula components: The TEXT function extracts the month name from the TODAY() date, while DATEVALUE assigns both months to a placeholder year (2000). The EDATE function handles the year wrap-around logic, ensuring DATEDIF never receives a negative date interval.

Calculate Dates Easily in WPS

Calculate Date Differences Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like LET, DATEVALUE, and DATEDIF, allowing you to calculate complex month intervals with zero compatibility issues. It provides a highly intuitive interface for all your data analysis needs.

1. Launch WPS Spreadsheet: Open your workbook containing the birth month and current date data.
2. Select the output cell: Click on the specific cell where you want the elapsed months to be displayed.
3. Enter the formula: Paste the LET formula containing DATEVALUE and DATEDIF directly into the formula bar.
4. Get your result: Press Enter to execute the formula and instantly view the calculated months.

Fully compatible with Microsoft Excel formulas and .xlsx filesNative support for advanced dynamic array functions like LETFree and lightweight suite for everyday data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does DATEDIF return a #NUM! error?

DATEDIF returns a #NUM! error if the start date is chronologically later than the end date. Ensure you are using the EDATE wrap-around logic to advance the end date by 12 months if it falls earlier in the year.

How do I convert a text month name into a month number?

You can use the formula =MONTH(DATEVALUE(A1 & " 1")) where cell A1 contains the month name (e.g., "August"). This trick tricks Excel into reading it as a date and returns its corresponding numerical value (8).

Can I calculate months without using the LET function?

Yes. While LET makes the formula easier to read, older versions of Excel can achieve the same result using a combination of the MOD and MONTH functions, such as =MOD(MONTH(B3)-MONTH(DATEVALUE(E2&" 1")), 12).

Excel & Data Problems