MONTH Function in Excel Tutorial

Excel Tutorial

Introduction

The MONTH function in Excel extracts the month number from a valid date. For example, if a cell contains 15-Nov-2026, the MONTH function returns 11 because November is the eleventh month of the year.

It is useful for analysing monthly sales, preparing employee records, organising transaction dates, and creating monthly reports.

1. What Is the MONTH Function in Excel?

The MONTH function extracts the month from a given Excel date and returns it as a number between 1 and 12.

For example, suppose a company has an employee joining date:

Joining Date
15-Nov-2026

To extract only the month number, use:

=MONTH(A2)

Expected Result: 11

Excel reads the date and returns its month number.

2. MONTH Function Syntax

=MONTH(serial_number)

Let’s understand the argument.

ArgumentMeaning
serial_numberA valid Excel date from which you want to extract the month.

For example:

=MONTH("15-Nov-2026")

Result: 11

The MONTH function requires only one argument.

3. Understanding Month Numbers in Excel

Excel represents the months of a year using numbers from 1 to 12.

Month NumberMonth Name
1January
2February
3March
4April
5May
6June
7July
8August
9September
10October
11November
12December

For example:

=MONTH("10-Jan-2026")

Result: 1

=MONTH("20-Aug-2026")

Result: 8

=MONTH("25-Dec-2026")

Result: 12

Important: MONTH returns the numerical month, not its name. If you provide a date in November, the result is 11, not November.

4. How Does the MONTH Function Work?

Excel stores valid dates internally as serial numbers. When you provide a date to MONTH, Excel reads the date and extracts its month component.

Suppose cell A2 contains 20-Aug-2026.

=MONTH(A2)

Excel reads the date as follows:

Date ComponentValue
Day20
Month8
Year2026

Final Result: 8

The original date remains unchanged. Only the month number is returned.

Important Points to Remember

  • MONTH returns a number from 1 to 12.
  • January is represented by 1.
  • December is represented by 12.
  • The input should be a valid Excel date.
  • If Excel cannot recognise the supplied value as a date, it may return a #VALUE! error.
  • The result is a number, not a month name.

Practical MONTH Function Examples

Question 1: Extract Month from a Date

Problem Statement

A company wants to extract the month number from an employee’s joining date.

Data

EmployeeJoining Date
Amit15-Nov-2026

Assume the joining date is in cell B2.

Excel Formula

=MONTH(B2)

Expected Result

11

Explanation

The date in B2 is 15-Nov-2026.

November is the eleventh month of the year, so Excel returns 11.

Concept Covered: Extracting a month number from a date.

Question 2: Extract Months from Multiple Employee Joining Dates

Problem Statement

An HR department wants to identify the joining month of every employee.

Data

EmployeeJoining Date
Amit15-May-2022
Neha10-Feb-2023
Rahul20-Aug-2024
Priya05-Jan-2025
Karan12-Nov-2026

Assume joining dates are in column B.

Excel Formula

=MONTH(B2)

Expected Results

EmployeeJoining DateMonth Number
Amit15-May-20225
Neha10-Feb-20232
Rahul20-Aug-20248
Priya05-Jan-20251
Karan12-Nov-202611

Explanation

Enter the formula in C2 and copy it down for the remaining employees.

Excel extracts the month number from each joining date.

Concept Covered: Extracting month numbers from multiple records.

Question 3: Extract Month from Sales Transaction Dates

Problem Statement

A sales manager wants to identify the month number of every sales transaction.

Data

Transaction IDTransaction DateSales
T10110-Mar-202425000
T10215-Jul-202430000
T10320-Jan-202545000
T10412-Sep-202550000
T10505-Nov-202660000

Assume transaction dates are in column B.

Excel Formula

=MONTH(B2)

Expected Results

Transaction IDTransaction DateSalesMonth Number
T10110-Mar-2024250003
T10215-Jul-2024300007
T10320-Jan-2025450001
T10412-Sep-2025500009
T10505-Nov-20266000011

Explanation

MONTH reads each transaction date and returns its month number.

For example, March is 3, July is 7, and November is 11.

Concept Covered: Extracting month numbers from sales records.

Question 4: Extract Month from Student Admission Dates

Problem Statement

A school wants to identify the admission month of every student.

Data

StudentAdmission Date
Rohan05-Apr-2023
Simran12-Jun-2024
Mohit18-Jul-2025
Pooja20-Mar-2026

Assume admission dates are in column B.

Excel Formula

=MONTH(B2)

Expected Results

StudentAdmission DateMonth Number
Rohan05-Apr-20234
Simran12-Jun-20246
Mohit18-Jul-20257
Pooja20-Mar-20263

Explanation

The MONTH function extracts only the month number from each student’s admission date.

Concept Covered: Extracting admission months from student records.

Question 5: Extract Month from a Date Entered Directly in the Formula

Problem Statement

A user wants to extract the month number from a specific date without referring to another cell.

Excel Formula

=MONTH("25-Dec-2025")

Expected Result

12

Explanation

The date is entered directly inside the formula. Excel recognises the date and MONTH returns 12 because December is the twelfth month.

Concept Covered: Providing a date directly to the MONTH function.

Question 6: Identify the Month of a Project Completion Date

Problem Statement

A company stores project completion dates and wants to identify the month in which each project was completed.

Data

ProjectCompletion Date
Website Development18-Aug-2025
Mobile Application10-Feb-2026
Database Migration25-Nov-2026
Software Testing15-Apr-2026

Assume completion dates are in column B.

Excel Formula

=MONTH(B2)

Expected Results

ProjectCompletion DateCompletion Month
Website Development18-Aug-20258
Mobile Application10-Feb-20262
Database Migration25-Nov-202611
Software Testing15-Apr-20264

Explanation

The formula reads each completion date and returns only its month number.

For example, August returns 8 and April returns 4.

Concept Covered: Extracting project completion months.

Question 7: Extract Month from Dates Across Different Years

Problem Statement

A company has transaction records from different years. The manager wants to extract the month number from each transaction date.

Data

RecordTransaction Date
Record A15-Aug-1995
Record B20-Jan-2000
Record C10-Jun-2010
Record D25-Dec-2026

Assume dates are in column B.

Excel Formula

=MONTH(B2)

Expected Results

RecordTransaction DateMonth Number
Record A15-Aug-19958
Record B20-Jan-20001
Record C10-Jun-20106
Record D25-Dec-202612

Explanation

MONTH extracts the month from each valid date, regardless of the year.

For example, January returns 1 whether the date belongs to 2000 or 2026.

Concept Covered: Extracting months from dates belonging to different years.

Student Practice Tasks

Try these tasks using the MONTH function.

Task 1: Employee Joining Month

Extract the month number from every employee’s joining date.

Task 2: Sales Transaction Month

Create a separate column showing the month number of every sales transaction.

Task 3: Student Admission Month

Extract the admission month from student admission dates.

Task 4: Project Completion Month

Display only the month number from each project’s completion date.

Task 5: Different Year Records

Extract month numbers from dates belonging to different years.

Final Student Challenge: Build a Monthly Employee Report

An HR department wants to prepare a report showing the joining month of every employee.

Employee Data

EmployeeDepartmentJoining Date
AmitIT15-Mar-2021
NehaHR20-Jun-2022
RahulFinance10-Jan-2023
PriyaIT25-Aug-2024
KaranSales05-Feb-2025
SimranHR18-Nov-2026

Your Challenge

  1. Add a new column named Joining Month.
  2. Extract the month number from every joining date.
  3. Apply the formula to all employees.
  4. Add two new employee records with different joining dates.
  5. Extract their joining month numbers as well.

Goal: Create an employee joining-month report using the MONTH function.

Key Takeaways

  • MONTH extracts the month number from a valid Excel date.
  • Its syntax is =MONTH(serial_number).
  • It requires only one argument.
  • The result is always between 1 and 12 for normal calendar dates.
  • January is 1 and December is 12.
  • You can use cell references or recognised date values.
  • MONTH is useful for sales, employee, student, and project reports.
  • The function does not change the original date.

Frequently Asked Questions (FAQs)

1. What is the MONTH function in Excel?

The MONTH function extracts the month number from a valid Excel date.

2. What is the syntax of the MONTH function?

The syntax is =MONTH(serial_number), where serial_number represents a valid date.

3. What does MONTH return for November?

MONTH returns 11 for any valid date in November.

4. Can MONTH extract the month from a cell reference?

Yes. If A2 contains a valid date, =MONTH(A2) returns its month number.

5. Does MONTH return the month name?

No. MONTH returns a number between 1 and 12, not the month name.

6. Can MONTH work with dates from different years?

Yes. It extracts the month regardless of the year.

7. Why does MONTH return a #VALUE! error?

This can happen when the supplied value is text that Excel cannot recognise as a valid date.

8. Does MONTH change the original date?

No. It only extracts and returns the month number. The original date remains unchanged.

9. Can MONTH be used in sales and employee reports?

Yes. It is useful for identifying transaction months, employee joining months, admission months, and project completion months.

10. What is the result of MONTH for December?

The result is 12 because December is the twelfth month of the year.