Spreadsheets

Add a month-and-year column from transaction dates for grouping

Add a month-and-year column in Excel from transaction dates: compare TEXT, DATE and EOMONTH and see which one sorts and groups correctly in pivots.

Editorial illustration for Add a month-and-year column from transaction dates for grouping

For monthly grouping, use a first-of-month date: =DATE(YEAR(A2),MONTH(A2),1), formatted as mmm-yyyy. It stays a real date, so it sorts in calendar order and plots correctly. The TEXT version looks the same but sorts alphabetically, with April before January.

The three ways to build the column

MethodFormulaTypeSorts in calendar order?
TEXT label=TEXT(A2,"mmm-yyyy")TextNo, April lands before January
TEXT, year first=TEXT(A2,"yyyy-mm")TextYes, as text
First of month=DATE(YEAR(A2),MONTH(A2),1)Real dateYes
End of month=EOMONTH(A2,0)Real dateYes

Take a hypothetical transaction on 18 March 2024. The TEXT formula returns the words Mar-2024. The DATE formula returns 1 March 2024, which is the number 45352. EOMONTH returns 31 March 2024, the number 45382. The two date versions are numbers underneath, which is the whole advantage.

Why does TEXT sort badly?

TEXT hands you a label, and Excel sorts labels as words: Apr-2024, Aug-2024, Dec-2024, Feb-2024 and so on. It also cannot tell that Jan-2025 comes after Dec-2024. A pivot table over these labels lists months alphabetically, and a chart treats them as unconnected categories with no time spacing. The year-first form, yyyy-mm, avoids the sort problem because the text order happens to match the calendar, but it is still a label, not a date.

Which one should I choose, first or last day?

  • First-of-month with DATE is the common choice, because the label means the month starting on that day.
  • EOMONTH(A2,0) is the natural fit if you will also work with month-end balances or due dates.
  • Another first-of-month form is =EOMONTH(A2,-1)+1, which gives the same answer as DATE for any date.

Whichever you pick, keep it throughout the workbook, so that a total labelled Mar-2024 always means the same thing.

How do I total by month once the column exists?

Say amounts are in column C and the month column is in F. Type the first of each month you care about down column H as real dates, then:

  • =SUMIFS($C$2:$C$500,$F$2:$F$500,H2) adds up every row whose month column equals H2.
  • Without a helper column, =SUMIFS($C$2:$C$500,$A$2:$A$500,">="&H2,$A$2:$A$500,"<"&EDATE(H2,1)) does the same, since EDATE(H2,1) is the first day of the next month.

Take three debits in March: ₹4,200, ₹1,850 and ₹960. Both formulas return 7,010, since 4,200 plus 1,850 is 6,050 and adding 960 gives 7,010.

A practical habit is to build the month column once, convert the data to a table with Ctrl+T, and let the formula fill itself when you paste in next month's rows. Pivots then refresh with one click.

What about pivot tables and charts?

Put the month column in Rows and the amount in Values. Because the field holds dates, Excel will usually offer to group them. If you right-click a date field and choose Group, tick Years as well as Months. Months alone merges every March from different years into one row, which is quite a lot of spending to lose track of.

In a chart, a date column lets Excel use a proper time axis. Gaps in months show up as gaps, rather than skipping.

Sometimes a plain number key is handier than a date. =YEAR(A2)*100+MONTH(A2) turns 18 March 2024 into 202403, which sorts correctly, can be used in SUMIFS without any date handling, and never merges two years. Avoid =MONTH(A2) alone as a grouping column. It returns 3 for every March, so March 2023 and March 2024 fall into the same bucket.

Checks before you build on it

  1. Confirm the column is numeric: =ISNUMBER(F2) should be TRUE. If it is FALSE, the source dates are still text and need converting first.
  2. Look at the first and last month in the list and compare them with the statement period.
  3. Count the months you expect, since a missing month in a pivot often means a transaction was read with the wrong date.
SPENDIXAI JOURNAL

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

Explore your reports