MONTH Function

Returns the month number from a date.

Category

Date & Time

Available in

All versions, Microsoft 365

Returns

A number from 1 to 12

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Dates & Times module

Overview

MONTH returns the month of a date as a number, 1 (January) to 12 (December).

It's the quickest way to group or filter dates by month in a helper column.

Syntax

=MONTH(serial_number) Explain

Arguments

ArgumentRequired?What it does
serial_numberYesThe date to read.

Examples

Example 1 — Month of each order

Add a helper column so orders can be summarised by month.

B2fx=MONTH(A2)
AB
1Order dateMonth
23/15/20263
37/4/20267
412/31/202612
=MONTH(A2) Explain

Example 2 — Total sales for March

Count only the rows whose month is 3.

=SUMPRODUCT((MONTH(A2:A100)=3)*B2:B100) Explain
For the month's name instead of its number, use =TEXT(A2, "mmmm") (March) or "mmm" (Mar).

Common mistakes

  • March of every year gets lumped together. MONTH ignores the year. Check YEAR as well, or use SUMIFS with a start and end date.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.