@Excel on Mac Date is Year Only Reverts to 1905

[BonnieB214]

I have a spreadsheet tracking a list of individual coins and their characteristics.  For each coin I have a cell with the 4-digit year (date) only - no months or days.  When I try to format this as a date (using custom - yyyy) the cell reverts to 1905.  There is no appropriate selection in the date format options.  Any way to format this cell to recognize the four digits (ex: 2023) as a year-only date?

[#2 · Accepted solution]

@BonnieB214 When you enter a 'year number' like 2023 and than format it as a date, Excel will turn it into the 2023rd day counting from January 1, 1900, which happens to be the 15th of July, 1905. That's where the 1905 comes from. In fact all 'year numbers' from 1828 to 2192 will turn into 1905.

If you want to use Excels time intelligence you need to enter dates properly. So, if your coin is from 2023, enter 1/1/2023 (or whatever format your local settings require) and then format it as a date with YYYY to show 2023.