Function Problems

How to Return Previous Month Name and Number in Excel

Aamir Naveed Akram Oct 9, 2026 872 views

Question details

The user needs Excel formulas to calculate and return the previous month's name and its numeric value based on a given month text, while ensuring output cells remain blank if the source cell is empty.

How to Return Previous Month Name and Number in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating previous month names and corresponding month numbers for dynamic date reporting, dashboards, or rolling schedules.
Observed behavior: Formulas return the correct previous month name (e.g., 'October' from 'November') and month number (e.g., 10), returning a blank output if no source date is provided to avoid formatting errors.

Before you start

Ensure that the source cells containing your month names are spelled correctly (e.g., 'January') so that the spreadsheet software can successfully parse them into valid dates for calculation.

Solution 1Recommended

Using TEXT, DATEVALUE, and IF Formulas

Combine the TEXT and DATEVALUE functions to accurately determine the previous month name and month number from a text string, using an IF statement to handle empty cells.

By appending a ' 1' to a text month (e.g., turning 'November' into 'November 1'), the DATEVALUE function can convert it into a serial date number. From there, standard date math can be applied to extract the previous month.

Calculate the previous month name

Select the target cell and enter =TEXT(DATEVALUE(J2&" 1")-1,"MMMM"), replacing J2 with your source cell containing the current month name. Press Enter to see the previous month.

Extract the corresponding month number

In a different cell where you want the numeric value, input the formula =MONTH(DATEVALUE(J2&" 1")) and press Enter.

Keep the result blank for empty source cells

To prevent error values (#VALUE!) when the source cell (e.g., C287) is blank, wrap your formula in an IF statement. For example, use =IF(C287="","",MONTH(C287)) to leave the cell completely blank if no data is present.

Formula Customization: Subtracting 1 from the first day of the current month effectively shifts the date to the last day of the previous month. The TEXT function's "MMMM" formatting then isolates just the full month name from that resulting date.

Professional Spreadsheet Editor

Calculate Dates and Formulas Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced date and text formulas like DATEVALUE, TEXT, and MONTH. You can easily manage financial reports, dynamic dashboards, and data analysis tasks with high efficiency and robust compatibility.

1. Open WPS Spreadsheet: Launch WPS Office on your device and create a new blank spreadsheet or open your existing workbook.
2. Enter Your Data: Type your current month names into a specific column (for example, column J).
3. Insert the Formula: Click on the cell where you want the result, type the formula =TEXT(DATEVALUE(J2&" 1")-1,"MMMM"), and press Enter.
4. Apply to All Rows: Click the small fill handle at the bottom-right corner of the active cell and drag it down to seamlessly apply the formula to the rest of the column.

Fully compatible with Microsoft Excel formulas, formatting, and file types (XLSX).Built-in function wizard helps you easily build and debug complex date calculations.Lightweight architecture runs smoothly on Windows, Mac, and Linux environments.Free to use with a familiar, user-friendly interface requiring zero learning curve.

Frequently Asked Questions

Why does DATEVALUE return a #VALUE! error?

This error occurs if the referenced cell contains unrecognized text or misspelt month names. Ensure your month name is spelled correctly (e.g., 'January') without trailing spaces so the spreadsheet can interpret it as a valid date string.

How can I return the previous month using a standard date format instead of a text month?

If your source cell contains a true date format (like 15/11/2023) rather than just a month name, you can use the EDATE function. Typing =EDATE(A2, -1) will subtract exactly one month from the date in cell A2.

Can I use the EOMONTH function to get the previous month?

Yes, if your source cell has a valid date, =EOMONTH(A2, -1) will return the last day of the previous month. You can then format the cell via 'Format Cells' to display only the month name (MMMM) or number.

How do I return the month name as a three-letter abbreviation?

Modify the TEXT function's format code from 'MMMM' (which outputs the full name) to 'MMM' (which outputs the abbreviation). The adjusted formula will look like =TEXT(DATEVALUE(J2&" 1")-1,"MMM").

Excel & Data Problems