MONTH in Google Sheets extracts a month number from a date: January is 1 and December is 12. Use =MONTH(A2) to read a date in A2, or combine MONTH with other formulas.
Extract Month Numbers from Dates and Orders
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Date | Sales |
|---|---|
| =DATE(2025,1,12) | 100 |
| =DATE(2025,3,5) | 250 |
| =DATE(2025,3,20) | 180 |
| =DATE(2026,3,8) | 120 |
| =DATE(2026,7,2) | 90 |
| =DATE(2026,12,1) | 80 |
Enter this formula in E2:
=MONTH(A2)The first result is 1, for January. The complete month sequence is 1, 3, 3, 3, 7, 12. The March dates return 3 even though they span different years.
The dataset uses DATE formulas to make the inputs unambiguous. Format A2:A7 as dates for display. A date’s appearance and its stored numeric value are different things.
MONTH Function Syntax
=MONTH(date)The date argument can be a cell containing a date, a DATE formula, or a date serial number. The result is a number, so use Automatic or Number formatting for the output cell.
An unquoted expression such as 7/2/2026 means division inside a formula. Use DATE or a date cell instead. Recognized date text may work, but parsing depends on the text and spreadsheet locale.
For explicit conversion, =MONTH(DATEVALUE("2026-07-02")) returns 7 in the tested US-locale sheet. Unrecognized date text returns #VALUE!; changing its display format alone does not repair it.
Extract Months for a Whole Column
=ARRAYFORMULA(MONTH(A2:A7))Enter this in a clear output column to return six month numbers at once. Leave six cells available for expansion. This is useful for grouping order dates across several years.
Blank dates need a check. MONTH of an empty cell returns 12 because an empty reference is treated as serial zero. To leave a blank input blank, use =IF(H1="","",MONTH(H1)).
Return a Month Name Instead of a Number
=TEXT(A2,"mmmm")This returns January. Use =TEXT(A2,"mmm") for Jan. These are text labels; keep the numeric month or original date when you need chronological sorting.
Total Sales for a Month
=SUMPRODUCT((MONTH(A2:A7)=3)*B2:B7)The result is 550: 250 + 180 + 120. MONTH identifies March in every year, and SUMPRODUCT adds the corresponding amounts. Both ranges must describe the same rows.
To total March 2025 only, use date boundaries with SUMIFS:
=SUMIFS(B2:B7,A2:A7,">="&DATE(2025,3,1),A2:A7,"<"&DATE(2025,4,1))This returns 430. The upper boundary is the first day of the following month, so timestamps throughout March remain included. Use numeric sales values for these examples.
Label a Date with Its Quarter
=IF(MONTH(A6)<=3,"Q1",IF(MONTH(A6)<=6,"Q2",IF(MONTH(A6)<=9,"Q3","Q4")))The July date in A6 returns Q3. Months 1–3 map to Q1, 4–6 to Q2, 7–9 to Q3, and 10–12 to Q4.
A shorter equivalent for valid dates is:
="Q"&ROUNDUP(MONTH(A6)/3,0)This also returns Q3. Check blank or invalid dates before assigning quarters, otherwise a missing date can be labeled as Q4.
Other Google Sheets articles you may also like