My file has dates that do not exist

A date column contains days that are not on the calendar: a 31st in a thirty-day month, or a 29 February in a year that has no such day. Nothing flagged them, and they exported the same way they arrived.

Why this happens

• Hand entry is the first source. A date typed into a cell formatted as text is just characters, and nothing checks that the characters describe a day that happened.
• A swapped day and month is the second and more insidious source. A US-style 02/31 read as day-first becomes the 31st of February, so a date that was merely written in the other convention becomes a date that cannot exist, and the impossibility is the evidence the reading was wrong.
• Leap years are the third. Every fourth year has a 29 February, except century years, except every fourth century, so 2000 had one, 1900 did not, and 2100 will not. Code that checks the day is between 1 and 31 accepts 29 February in all of them.
• The cost is downstream rather than here. A database column, a date library, or an API will refuse 2026-02-31 outright, so a file that imported cleanly fails at the point where it is finally used, which is usually further from the sender and later than you would like.

What to do right now

• Check the day against the month rather than against 31. That single test finds every one of these and is a filter you can write in one formula.
• Treat an impossible date as evidence about the whole column, not as one bad row. If a 31st appears in a second position, the column is probably day-first and read as month-first.
• Go back to the sender for the real value. An impossible date cannot be repaired by inference, because both the day and the month are candidates for being the wrong one.

Stopping it on the next file

A date field checks that the day exists in that month, in that year, so 31/02/2026 is refused and named rather than exported as “2026-02-31” for something downstream to choke on. February is checked against the actual year rather than assumed, which means 29/02/2027 is refused and 29/02/2028 is accepted. It is a small rule and it is the difference between a file that imports clean and breaks later, and one that tells you now.

The invoice line items template is a sheet with those rules already set, if you want to see the shape of one before building your own.