Function Problems

Why Excel MONTH(2) Returns January and How to Fix It

John Wilson Nov 3, 2026 869 views

Question details

The user is trying to extract the month number using the formula =MONTH(2) but gets 1 (January) instead of the expected 2 (February).

Why Excel MONTH(2) Returns January and How to Extract the Correct Month

Product: Excel
Device & OS: not provided
Scenario: Attempting to generate a specific month number or month name using a static number inside a date function.
Observed behavior: Excel evaluates the static number 2 as the date serial number for January 2, 1900, causing the MONTH function to return 1.

Before you start

Ensure that the cells you are referencing in your formulas contain correctly formatted dates rather than static integers or text strings.

Solution 1Recommended

Reference an Actual Date Cell in the MONTH Function

To get the correct month number, reference a cell containing a properly formatted date instead of entering a static integer directly into the formula.

Spreadsheet software treats whole numbers inside date functions as serial numbers. The number 1 represents January 1, 1900, and 2 represents January 2, 1900. Thus, when you input =MONTH(2), the system looks at the date January 2, 1900, and correctly outputs 1 for the first month.

Enter a valid date

Click on an empty cell (e.g., A1) and type a standard date, such as 2/15/2023.

Input the MONTH formula

Click on a different cell where you want the result to appear and type =MONTH(A1).

Calculate the result

Press the Enter key. The cell will now display 2, representing February.

Reference an Actual Date Cell in the MONTH Function

Direct Date Input: If you do not want to use cell references, you can type the date directly into the formula using quotation marks, like this: =MONTH("2/15/2023").

Solution 2

Use the TEXT Function to Display the Full Month Name

If your goal is to display the word 'February' rather than the number 2, you should use the TEXT function on a date cell.

Master Data Calculation

Manage Date Functions Flawlessly with WPS Spreadsheet

WPS Spreadsheet is a powerful, free tool that perfectly handles complex date and time functions just like Microsoft Excel. It offers built-in formula guidance to prevent serial number errors and ensures your data is calculated accurately.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
2. Access the Formula Tab: Navigate to the 'Formulas' tab on the top ribbon.
3. Select Date and Time Functions: Click on the 'Date and Time' icon to browse available functions like MONTH, DAY, and YEAR.
4. Follow the Prompt: Select your function and follow the dialog box to pick the correct cell references, avoiding static number errors.

100% compatible with Microsoft Excel (.xlsx) formulas and date serial numbersBuilt-in function wizard helps you select parameters correctlyCompletely free to use with a lightweight and intuitive interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do spreadsheets use date serial numbers?

Spreadsheets store dates as sequential serial numbers so they can be easily added, subtracted, and used in calculations. By default, the serial number 1 corresponds to January 1, 1900.

How do I convert a month name text back to a month number?

You can combine the MONTH and DATEVALUE functions. If cell A1 contains the text 'February', use the formula =MONTH(DATEVALUE(A1&" 1")) to return the number 2.

Does the MONTH function work with different regional date formats?

Yes, the MONTH function will evaluate dates based on your system's regional settings. However, referencing a properly formatted date cell is the safest way to ensure accuracy regardless of regional configurations.

Excel & Data Problems