In an expense spreadsheet, the date column causes more trouble than any other. The totals add up, but months won't group and sorting comes out in a strange order. Almost always the cells only look like dates. Here are the most common symptoms, what causes them and how to fix them.

First, Excel dates are numbers

Excel stores a date as a serial number counting days from January 1, 1900, then formats it to look like a date. September 13, 2026 is 46278, for example. That's why you can subtract two dates to get the days between them, and why grouping and sorting work. A cell that merely looks like a date but contains text can't do any of that.

Symptom 1: dates show as numbers like 46278

The value is a real date but the format has become General. Select the column, press Ctrl+1 (⌘+1 on a Mac), and choose Date, or enter a custom format such as yyyy-mm-dd. This often happens after pasting values from another file.

Symptom 2: date cells show

The column is too narrow to display the date. Double-click the right edge of the column header to autofit it. Negative values that can't be shown as dates also display #####, so if widening doesn't help, check the value itself.

Symptom 3: dates sit on the left and won't sort

The dates are text. By default Excel aligns numbers and dates to the right and text to the left. This is common with dates copied from websites or other programs.

  • Mixed separators: select the column and use Find and Replace (Ctrl+H) to change . to / or -. Excel usually recognizes the result as dates.
  • Many formats at once: select the column, choose Data > Text to Columns > Next > Next, set the column format to Date, pick the order your data uses (such as MDY), and click Finish.
  • With a formula: put =DATEVALUE(B2) in a spare column and apply a date format. An error means Excel can't interpret that text.

Symptom 4: day and month are swapped

A date like 03/04/2026 is March 4 in the US and April 3 in most other countries. It happens a lot with CSV files from services in another region. Rather than double-clicking the CSV, import it and set the date order in Text to Columns to match the file (for example DMY for a UK export). Wrongly converted dates are hard to spot later, because only days from 1 to 12 get silently swapped.

The receipt tool treats ambiguous dates the same way: it doesn't guess silently, it highlights the cell for a check.

Symptom 5: dates are off by one day

When another program exports dates with a time and a time zone conversion happens along the way, dates near midnight can slip to the previous day. Export again with dates only, or strip the time from a date-time cell with =INT(B2) and check it.

ReceiptMoa avoids this by writing dates into the file as serial numbers with no time attached.

Symptom 6: dates are four years apart after copying

Some very old Mac Excel workbooks count dates from 1904. Mixing those with modern files shifts dates by four years and a day (1,462 days). Check that File > Options > Advanced > Use 1904 date system is set the same way in both workbooks.

Summary

Date trouble starts with one question: is the cell a number or text? Right-aligned and happy to change format means a date; left-aligned means text. Convert text with Find and Replace or Text to Columns, and always set the date order when importing files where the day and month could be read either way.