Formula Errors

How to Return the Previous Month in Excel (Fix January to December)

Emma Brown Oct 7, 2026 868 views

Question details

The user needs an Excel formula that correctly calculates the previous month, specifically returning December when the current month text is January.

How to Calculate the Previous Month in Excel (January to December)

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Calculating previous month names or dates across year boundaries using text inputs.
Observed behavior: Basic formulas may incorrectly return January or result in an error instead of rolling back to December when the current month is January.

Before you start

Ensure that your month data is formatted consistently as text (e.g., 'Jan', 'January') or as a valid date, as understanding whether your cell contains a text string or a serial date value is crucial for choosing the correct formula.

Solution 1Recommended

Use the EOMONTH and TEXT Functions to Calculate the Previous Month

The most reliable method to handle year boundaries (January to December) is converting the month text to a date value and using the EOMONTH function.

The EOMONTH function calculates the last day of the month a specified number of months in the past or future. By combining it with DATEVALUE and TEXT, you can seamlessly handle text-based month names and roll back from January to December without calculation errors.

Locate the target cell

Identify the cell containing your current month name (for example, 'Jan' or 'January' in cell B2).

Enter the EOMONTH formula

In an empty cell where you want the previous month to appear, type the formula =TEXT(EOMONTH(DATEVALUE(B2&"1"),-1),"mmm").

Press Enter and apply

Press Enter to see the result. If B2 is 'Jan', the formula will correctly output 'Dec'. You can then drag the fill handle to apply this formula to other cells in your column.

Use the EOMONTH and TEXT Functions to Calculate the Previous Month

Format adjustment: Change 'mmm' to 'mmmm' in the formula if you want to output the full month name (e.g., 'December' instead of 'Dec').

Solution 2

Use EOMONTH for Standard Date Values

If your cell already contains a standard Excel date rather than just the month text, a simpler EOMONTH formula can be used.

Seamless Formula Calculations

Use WPS Spreadsheet to Calculate Dates Without Errors

WPS Spreadsheet offers full compatibility with standard Excel functions, including EOMONTH, DATEVALUE, and TEXT. It allows you to process complex date calculations effortlessly for free.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the date or month data.
2. Select a blank cell: Click on the cell where you want the previous month calculation to appear.
3. Insert the formula: Type =TEXT(EOMONTH(DATEVALUE(B2&"1"),-1),"mmm") into the formula bar and press Enter.
4. Format and adjust: Use the built-in Format Cells dialog in WPS Spreadsheet to customize the date appearance to your exact needs.

Fully compatible with Microsoft Excel formulas, cell formatting, and date functions.Easily handles year-boundary transitions like calculating January back to December.Lightweight and free alternative for complex data analysis and daily productivity.Familiar user interface makes it easy to switch without learning new skills.

microsoft office alternative - wps office

Frequently Asked Questions

Why does a simple minus 30 days formula fail when finding the previous month?

Subtracting 30 days from a date doesn't account for months with 28, 29, or 31 days. Using the EOMONTH function ensures accurate month-to-month calculations regardless of the specific month's length.

Can I return the previous month and year together?

Yes, you can modify the TEXT function format. Use =TEXT(EOMONTH(DATEVALUE(B2&"1"),-1),"mmm yyyy") to return a format like 'Dec 2023'.

What if my month text is a full word like 'January' instead of 'Jan'?

The DATEVALUE function paired with &"1" handles full month names perfectly. Just ensure your TEXT function is set to "mmmm" to output the full name of the previous month.

Does the EDATE function work the same as EOMONTH for this scenario?

EDATE returns the exact same day in the previous or future month, whereas EOMONTH always returns the last day of the target month. For extracting just the month name via the TEXT function, both will work effectively.

Excel & Data Problems