There’s a useful but niche Excel warning about date with two digits for the century. That’s a useful precaution but sometimes the warning is wrong!
Those of us around at the turn of the century will remember the Y2K problems and possible mistakes with a date that has only two digits. For example, “15 April 51” – is that 1951 or 2051?
That’s why Excel has a warning about 2-digit years like this:
Choose to ignore the error or convert the text to add either 19xx or 20xx . The cell remains as text, not converted to an Excel date.
But sometimes Excel wrongly thinks the text has a two digit year when it’s not. This cell has “8/21” as text which could mean August 1921 or August 2021, which is what Excel suspects. But it could also mean the 21st of August, depending on how dates are formatted in your part of the world.
Interestingly, a cell with a second digit that’s 12 or lower (say “8/11”) doesn’t trigger an error, presumably because it could be a month/year combination.
Text only that looks like a date
The two digit year warning only appears for TEXT that looks like a date.
There’s no warning for a date cell that’s formatted with just two year digits because the underlying value is always full year. Compare the full date in the formula bar with the displayed date in the cell.
Good for imported data
The two digit year warning is great for an extra check on dates being imported from the likes of CSV files.
Cells containing years represented as two digits
Turn the warning on/off from File | Options | Formulas | Error checking rules | “Cells containing years represented as two digits”
