Spreadsheets

Extract month and year from a date column in an expense sheet

Add a month and year helper column in Excel with MONTH, TEXT and a sortable key, and learn which version works best for SUMIFS and pivot tables.

Editorial illustration for Extract month and year from a date column in an expense sheet

To group expenses by month, add a helper column next to the date. =MONTH(A2) gives 1 to 12, =TEXT(A2,"mmm-yy") gives a label such as Apr-25, and =EOMONTH(A2,-1)+1 gives the first day of the month as a real date that sorts correctly. For most sheets the first-of-month date is the best key.

What does each formula return?

Say A2 holds 14 April 2025.

FormulaReturnsGood for
=MONTH(A2)4Quick filters within one year
=TEXT(A2,"mmm-yy")Apr-25 (text)Readable labels on charts
=TEXT(A2,"mmmm")April (text)Titles and headings
=TEXT(A2,"yyyy-mm")2025-04 (text)Sorting labels in order
=EOMONTH(A2,-1)+11-Apr-2025 (a real date)SUMIFS, pivots and sorting

MONTH alone loses the year, so April 2025 and April 2026 look the same. If your sheet covers more than twelve months, use one of the others.

Why is text like Apr-25 awkward to sort?

Text sorts alphabetically, so Apr-25 comes first, then Aug-25, then Dec-25, which is not the order of the calendar. A yyyy-mm label such as 2025-04 does sort in date order, because the year comes first and the month is always two digits. The real date from EOMONTH sorts properly too, and you can display it as Apr-25 by formatting the column with the custom format mmm-yy, without changing the underlying value.

How do I use the month in a SUMIFS?

  1. Make column D the helper: =EOMONTH(A2,-1)+1, formatted as mmm-yy, and fill down.
  2. In G2 type the first day of the month you want, such as 1-Apr-2025.
  3. In H2 enter =SUMIFS($C$2:$C$500,$D$2:$D$500,G2,$B$2:$B$500,"Food").
  4. Fill G down with =EDATE(G2,1) to produce a month for each row, and H follows.

Both sides are real dates, so there is nothing to misread. Be careful with text keys such as 2025-04 as the criterion. Excel may try to interpret a criterion that looks like a date, so test a text key on a sample before trusting it.

Will a pivot table do the grouping for me?

Yes. If column A holds real dates, add the date field to Rows in a PivotTable, right-click a date, choose Group, and select Months and Years. You will get a row per month without any helper column. The helper column is useful when you also want SUMIFS formulas, filters in the sheet or a month that follows your own rule, such as a month that starts on the day your salary arrives.

What if the month shows as a number like 45761?

That is the date's serial number, which is how Excel stores dates. Format the cell as a date, or as Custom with mmm-yy, and the number turns into a readable month. If TEXT returns the format code itself, such as mmm-yy printed literally, check your language setting. In some regional versions of Excel, the format letters for month and year differ, so look up the right letters for your version.

Which one should I pick?

  • One year of data and quick filtering: MONTH.
  • Charts and printed reports: TEXT with mmm-yy.
  • Anything you will sort, sum or pivot across years: the first-of-month date.

If you add rows constantly, turn the range into an Excel Table with Ctrl+T. A formula typed in one cell of a Table column fills down automatically for every new row, so the helper column grows with the data, and SUMIFS ranges written as table columns expand by themselves. That removes the habit of dragging formulas to row 500.

When a sheet stops being enough

If the helper columns keep multiplying, SpendixAI includes a Report builder that groups transactions without formulas, along with a library of 25 ready-made reports grouped by question, so month-wise totals do not need helper columns at all.

Quick checklist

  • Dates tested with ISNUMBER.
  • Helper column filled to the last row, or built on a Table.
  • Sorted a sample to see that months run in calendar order.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports