FP

FormulaPilot

Spreadsheet formula tools

Open generator
Excel how-to guide

How to month year extract in Excel

Create a month column from a transaction date so you can group or filter data by month without a pivot table.

beginnerDate & TimeMONTH reference

Step-by-step instructions

How to month year extract — 3 steps

1

Use YEAR(A2) to extract the four-digit year from the date.

2

Use MONTH(A2) to extract the month as a number from 1 to 12.

3

Wrap MONTH in TEXT with format "00" to zero-pad single-digit months, then join with the year using &.

Example data

Worked example

Transaction DateYear-Month
2026-01-152026-01
2026-03-022026-03
2026-11-282026-11

Common mistakes

Errors to watch out for

Month shows as a date

MONTH returns an integer, but if the cell is formatted as a date it may display as a date serial. Change the cell format to General or Number.

Sort order is wrong

Jan, Feb, Mar sorts alphabetically and gets the order wrong. Use the YEAR-MM format (e.g. 2026-03) so chronological sorting works correctly.

FAQ

Frequently asked questions

How do I get the full month name like January?

Use TEXT(A2,"MMMM") to return the full month name, or TEXT(A2,"MMM") for the abbreviated version.

Need a custom variation?

Describe your exact spreadsheet problem — get the formula in seconds.

Open AI Formula Generator — Free

Related how-to guides

Continue learning.