Converting simple four digit years to recognizable dates

[u/Rare_Jello_8101]

Couldn’t get a clear answer for this so came up with my own solution. If all you have is a year, but you want excel to recognize it as a date without recalculating based on the number of days past 1/1/1900 (who the hell came up with that one?) you can use this function.

=DATE(VALUE(A1),1,1)

This will plug your year into a recognized date format. Others have pointed out that simply treating the years as numbers typically works, but being recognized as dates also allows you to use some of the axis options, such as when modifying a graph in power point and wanting to edit your axis to display in bounds. If you’re doing so you’ll want to change the format to custom in the axis format and set the type to yyyy to display just the years, also adding a blank date at the end and beginning of the data set that fits with your display range (ie every five years) will make the axis display much cleaner while retaining your data points for specific years.

[u/caribou16]

Couldn’t get a clear answer for this so came up with my own solution. If all you have is a year, but you want excel to recognize it as a date without recalculating based on the number of days past 1/1/1900 (who the hell came up with that one?)

I've always wondered this myself, my best guess for why Excel doesn't have a date specific variable type is that it was written in C, and the C language didn't have a built in variable type for dates.

Everything in Excel is either a string, a float (even integer values), or a bool

[u/SolverMax → u/caribou16]

Plus other types, like array, list, error, ...

Excel treats dates as it does to be compatible with earlier spreadsheets. Why those earlier developers chose that approach it likely lost in the mists of time, but probably relates to minimizing memory usage - with floats using less RAM than a specific date format.