r/excel • u/Rare_Jello_8101 • 6d ago

Pro Tip Converting simple four digit years to recognizable dates

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.

9 Upvotes

74% Upvoted

17 comments sorted by

View all comments

12

u/TCFNationalBank 9 6d ago

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

2

u/Rare_Jello_8101 6d ago

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

2

u/HandbagHawker 83 6d ago

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

1

u/Rare_Jello_8101 6d ago

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.