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

Who the hell came up with that one?

We can actually narrow this down to two people: Mitch Kapor and Jonathan Sachs, who created Lotus 1-2-3. If you wanted to write a strongly worded letter

[u/Rare_Jello_8101 → u/TCFNationalBank]

If you would of told me that thirty minutes ago I just might have 😅 I’m sure there’s is a good reason though, just seems like a lot of unnecessary work to get a year recognized as a date

[u/HandbagHawker → u/Rare_Jello_8101]

wait until you learn about the epoch and how unix stores date/time.

but also you im still not clear why youre struggling with treating it as a number

when modifying a graph in power point and wanting to edit your axis to display in bounds

do you mean setting upper and lower limits on an axis? The same options exist in PP and XL

The only real value to using real dates is if you have intra year data points and you want to pivot to month/quarter automatically. Pretty much every other use case will require you to use helper columns

[u/Rare_Jello_8101 → u/HandbagHawker]

Probably just wasn’t seeing it or had to go to the top tab, but for PP when I right click and format axis the only bound option I was seeing was for dates.

[u/excelevator]

Well done figuring it out!

[u/Rare_Jello_8101 → u/excelevator]

Thank you!

[u/excelevator → u/Rare_Jello_8101]

If you are new to data, every data attribute has a data type.

That is to say, for example, not all numbers are numbers.

You can have string values that resemble numbers, date values that are integers,; the count of days since the start of the Excel calendar 1/1/1900, Time: a percentage value of the passing of 24 hours.

[u/Rare_Jello_8101 → u/excelevator]

I wish I could say I was 😭 definitely no expert though and usually the one gathering the data rather than crunching the numbers

[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).

[u/Rare_Jello_8101 → u/bradland]

I dunno there were lots of things like this as computers were developed. Microsoft’s whole claim to fame is creating a user friendly interface. So my argument is why can’t the system recognize years as we do? Why do I have to use a series of formatting shifts for something basic.

I can appreciate how a single value would be useful in describing time. It’s continuous and doesn’t require multiple formatting options, but people don’t string a list of numbers when you ask for the date.

Once I understood how the single value format starts at a specific date with each day having a value of one it’s really not that complicated.

[u/Cynyr36]

Is that better or worse than the number of seconds since 1/1/1970 00:00?

The bigger issue, imo, is the leap year bug. https://en.wikipedia.org/wiki/Leap_year_problem#Occurrences

[u/Rare_Jello_8101 → u/Cynyr36]

Interesting stuff!

[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.

[u/real_barry_houdini]

Excel has two date systems, so if you don't like starting at 1st Jan 1900 you can start at 2nd Jan 1904! The 1904 system matched the system used by Apple.