If your monthly total returns zero, or leaves out half the month, the likely culprit is dates stored as text. A cell that looks like 05/03/2026 but is text will not match a date comparison, so the formula skips it. Test the column, convert it to real dates, and then rebuild the monthly formula.
How do you tell a real date from text?
Real dates are numbers underneath. Type =ISNUMBER(A2) beside the first date. TRUE means a real date; FALSE means text. Text dates often sit left-aligned and refuse to change format when you pick a different date style. Another test is to format the cell as General: a real date turns into a number such as 46086, while text stays as typed.
| Cell shows | ISNUMBER result | Meaning |
|---|---|---|
| 05/03/2026, right-aligned | TRUE | Real date |
| 05/03/2026, left-aligned | FALSE | Text, formulas skip it |
| 5 March 2026 typed with an apostrophe | FALSE | Text |
How do you convert text dates?
- Select the date column.
- Open Data, then Text to Columns, and click Next twice.
- On the last step choose Date and pick the day-month-year order.
- Click Finish and run =ISNUMBER on a few cells.
- Format the column as a date style you like.
If dates have been entered in mixed orders, fix by hand the rows where the day is above 12, since those are the only unambiguous ones, and treat the rest as day first.
What should the monthly formula be?
Put the first day of the month in F1, for example 1/03/2026, and the first day of the next month in F2. Then use =SUMIFS($C$2:$C$500,$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<"&$F$2). The amounts are in column C and the dates are in column A. Using less-than the next month's first day avoids the common mistake of missing transactions on the last day.
A worked example with A$ figures
Say March has three rows: 4 March A$85.00, 18 March A$240.50 and 31 March A$60.00. A formula that stops at 30 March would report 85.00 + 240.50 = 325.50 and miss the last row. The next-month-start rule reports 85.00 + 240.50 + 60.00 = 385.50. If one of the three dates is text, it vanishes from either version, which is why the ISNUMBER test comes first.
Using fewer formulas
Some people avoid date formulas by adding a Month column with =TEXT(A2,"yyyy-mm") and summing on that. It reads well but fails on text dates too, so convert first. If the monthly rebuild still feels fragile, SpendixAI is built around India and INR, so it is not aimed at Australian dollar sheets, but its reports are generated from the transactions, not hand-built formulas.
What if dates arrive in several formats?
Pasted bank data sometimes mixes real dates and text dates in one column, which is why a formula can seem to work for some rows and not others. Sort the column and look at where the alignment changes. Rows on the left are probably text. Fix them in a batch rather than one by one, then add a check cell with =COUNT($A$2:$A$500) beside =COUNTA($A$2:$A$500). The two numbers should match, and a mismatch tells you how many dates still need attention.
Should you keep a separate month column?
A helper column can make reports easier to read. In column E, enter =EOMONTH(A2,-1)+1 to return the first day of the date's month, fill down, and format it as a month. Then a total by month needs only the category and that month cell. It also helps with sorting. Remember that EOMONTH needs a real date in column A, so the ISNUMBER test still comes before any of this.
Checklist
- Dates pass ISNUMBER
- Next-month-start used as the upper bound
- Monthly totals sum to the full total
- Only unambiguous dates edited by hand
- Original data kept untouched
Useful ideas start with a clearer picture of your own spending.
Explore your reports

