Changing a year (yyyy) to date format

August 30, 2011 at 13:30:42
Specs: Windows 64
I have a column of years in excel that I am trying to plot with another column of numbers in a bar graph. However, because the cells the years are in are not formatted as dates, excel is reading them as numbers, plotting them on the wrong axis, and not using the exact number. When I try to format the years cells to dates, there is no option to just list the year, and if I choose one that has a day, month, and year (mm/dd/yyyy for ex), it totally changes my original data (ie I have the year 1983 as one of my years and if I click on the mm/dd/yyyy option, it changes it to 6/5/05, etc). How do I format these cells without it changing the data I already have in it? Or, how do I plot this data as is?

See More: Changing a year (yyyy) to date format

Report •

August 30, 2011 at 15:23:05
You need to understand how Excel stores dates, here is a quote from MS:

"Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m.
Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59.
The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion.
For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m."

So, with that being said, just entering the year 1983, as you know, gives you a Date of 6/5/1905 or 1983 days since 1/1/1900.

You cannot get just the Year alone, as Excel does not understand it as a Date.
You could try using 01/01/1983 and then using a Custom Format to display it as just the year by using YYYY.


Report •

August 30, 2011 at 15:45:37
Perhaps if you gave us a few more details as to what you are plotting, we could offer a more specific suggestion.

For example, give us a small sample of your data, what data is used for what axis, what type of chart you are trying to create, etc.

Before you post any data, please click on the following line and read the instructions on how to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 31, 2011 at 10:09:40
Thank you to both of your replies. I did not understand the 1905 part until you explained it and now it makes so much more sense! After playing around with everything I actually ended up being able to create a scatter plot with the data and for some reason it allowed me to put the data I wanted to on the axis where I wanted it, and then I was able to change the type of graphic to the bar graph. Don't know how or why this worked, but it solved my problem so I didn't complain. Thanks again for your responses!

Report •

Related Solutions

Ask Question