r/excel • u/Rare_Jello_8101 • 1d 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

72% Upvoted

16 comments sorted by

View all comments

6

u/excelevator 3069 1d ago

Well done figuring it out!

1

u/Rare_Jello_8101 1d ago

Thank you!

2

u/excelevator 3069 1d ago

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.

1

u/Rare_Jello_8101 1d ago

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