MONTH function

Returns the month (1–12) from any date; use it to organize timesheet entries, identify due dates by month, or detect multi-month tasks.

=MONTH(serial_number)

Generate a MONTH formula

Describe what you need. The generator will reach for MONTH where MONTH is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How MONTH reads its arguments
serial_numberrequiredMONTH →
ArgumentRequiredDescription
serial_numberRequiredA date value, either as a date serial number, a text string in date format (like "1/15/2026"), or a cell reference containing a date. If empty or not a date, returns #VALUE! error.

Returns

An integer from 1 to 12, where 1 is January and 12 is December.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Extract start month to organize timesheet by month

TaskOwnerStart DateDue DateHours Logged
Design mockupsSarah1/15/20262/20/202618
Backend APIJames2/10/20264/5/202645
Testing phaseMaria4/1/20265/15/202622
DocumentationAlex3/15/20263/30/20268
=MONTH(C2)

Result: 1

MONTH extracts the month component from the start date in C2 (1/15/2026), returning 1 for January. Use this pattern to group all tasks by their start month and analyze when the heaviest workload falls.

2. Identify tasks due in a specific month

TaskOwnerStart DateDue DateHours Logged
Design mockupsSarah1/15/20262/20/202618
Backend APIJames2/10/20264/5/202645
Testing phaseMaria4/1/20265/15/202622
DocumentationAlex3/15/20263/30/20268
=IF(MONTH(D3)=4,"April","Other")

Result: April

MONTH extracts 4 from the due date in D3 (4/5/2026). The IF statement checks if this equals 4, and since it does, returns "April". This approach filters or highlights all tasks with deadlines in a specific month, useful for sprint planning.

3. Display task duration by showing start and due months

TaskOwnerStart DateDue DateHours Logged
Design mockupsSarah1/15/20262/20/202618
Backend APIJames2/10/20264/5/202645
Testing phaseMaria4/1/20265/15/202622
DocumentationAlex3/15/20263/30/20268
=MONTH(C2)&"-"&MONTH(D2)

Result: 1-2

MONTH extracts 1 from the start date (1/15/2026) and 2 from the due date (2/20/2026), then the & operator concatenates them as "1-2". This quickly identifies that Design mockups spans January through February, helping you spot multi-month tasks for resource planning.

Common errors

Which MONTH error are you seeing?
MONTH returned an error#VALUE!
Ensure the cell contains a valid date or recognized date text like "1/15/2026". Use DATE(2026,1,15) to construct an explicit date, or wrap text in DATEVALUE: =MONTH(DATEVALUE("1/15/2026")).
#NUM!
Verify the serial number is within the valid range. Use DATE() to construct dates explicitly instead of raw numbers: =MONTH(DATE(2026,1,15)). Check imported data for conversion errors.
#N/A
Wrap the lookup in IFERROR to supply a default date: =MONTH(IFERROR(VLOOKUP(...),DATE(2026,1,1))). Verify the lookup value actually exists in your data range.
ErrorWhy it happensHow to fix it
#VALUE!serial_number is text that cannot be interpreted as a date, or refers to a cell containing text without date format.Ensure the cell contains a valid date or recognized date text like "1/15/2026". Use DATE(2026,1,15) to construct an explicit date, or wrap text in DATEVALUE: =MONTH(DATEVALUE("1/15/2026")).
#NUM!serial_number is a number outside Excel's valid date range—before 0 (1/1/1900) or after 60109 (12/31/9999).Verify the serial number is within the valid range. Use DATE() to construct dates explicitly instead of raw numbers: =MONTH(DATE(2026,1,15)). Check imported data for conversion errors.
#N/Aserial_number comes from a lookup formula (VLOOKUP, XLOOKUP, etc.) that returns #N/A because no match was found.Wrap the lookup in IFERROR to supply a default date: =MONTH(IFERROR(VLOOKUP(...),DATE(2026,1,1))). Verify the lookup value actually exists in your data range.

Tips and when to use something else

  • Use YEAR() and DAY() to extract other date components from the same cell—for example, =YEAR(C2) returns 2026 and =DAY(C2) returns 15.
  • To convert MONTH's numeric result to a month name, use =TEXT(DATE(2026,MONTH(C2),1),"mmmm"), which returns "January" for month 1 or "mmm" for abbreviations like "Jan".
  • If you need the last day of the extracted month, use EOMONTH(serial_number, 0) instead—it's purpose-built for month-end calculations and returns a date.
  • Use DATEDIF(start, end, "M") when calculating months *between* two dates, rather than MONTH for extracting the month component; DATEDIF counts complete months elapsed.

Related functions

YEARYEAR extracts the four-digit year component from a date serial number, returning an integer representing the calendar year, such as 2024 or 2025.DAYDAY extracts the day of the month (1-31) from a date serial number, useful for filtering tickets and grouping records by calendar day.DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.EOMONTHReturns the date of the last day of the month that is a specified number of months before or after a given start date.DATEDIFReturns the number of time units (days, months, years) between two dates, useful for calculating durations, aging, or time-to-deadline.

Frequently asked questions

How do I get the month name instead of the number?
Use =TEXT(DATE(YEAR(date),MONTH(date),1),"mmmm") to return the full month name ("January", "February", etc.). For three-letter abbreviations, replace "mmmm" with "mmm" to get "Jan", "Feb", etc.
Do MONTH results differ between Excel and Google Sheets?
No—both use the same serial number system and return 1–12. However, Google Sheets sometimes displays dates as text, so wrap the argument in DATEVALUE if needed: =MONTH(DATEVALUE(C2)).
Why does MONTH return 1–12 instead of 0–11?
Excel and Google Sheets use 1-based month numbering to match real-world usage (January = 1, December = 12). If you need 0-based indexing for array formulas, subtract 1: =MONTH(date)-1.
How do I filter rows by multiple specific months?
Use OR to test multiple conditions: =IF(OR(MONTH(date)=1,MONTH(date)=3,MONTH(date)=5),"Match","No") for January, March, or May. For longer lists, use COUNTIF: =IF(COUNTIF({1;3;5},MONTH(date)),"Match","No").

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17