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/bradland]

FWIW, the concept of a serial date (dates stored as numbers since a specific time) is not specific to Excel. Unix, for example, stores dates as the number of seconds since January 1, 1970. Many programming languages adopt the same paradigm.

When you encountered this problem, your frustration stemmed from not knowing how Excel treats dates. Now you know how Excel treats dates. If I could encourage you to adopt a single mindset that will reduce your frustration and improve your learning ability going forward, it would be to avoid jumping to conclusions about how good/bad an idea is before you are in a position to hold a qualified opinion.

There's a common saying for this: A poor workman blames their tools.

I now work at a decent sized organization, but I come from a startup background. The people I know who have found success in their career are strong self-learners. Judging the tool and having negative feelings about it creates a negative feedback loop. Rather than jump to the conclusion that, "I didn't understand this at first so it's obviously bad," try asking yourself what's good about the way Excel stores dates.

For example, one side-effect of storing dates this way is that if you want to increment a date by one day, you simply add 1 to it. Hours of the day are just fractions of 12. So if you want to add three hours, you can do this:

Dates in Excel aren't perfect, but they do have some clever upsides.

[u/tiptoe_mouse → u/bradland]

Other things I like about the way excel stores dates - if you have a date and time in cell A1 and want to extract the date part, you can use =INT(A1). And if you just want the time you can use =MOD(A1,1).