Dates Not Recognized as Dates: Numeric Years in Excel 2007:-


If you are plotting data where the only identifier is a numeric year, Excel does not automatically recognize this field as a date field.

In the Figure (given below), for example, data is plotted once a decade for the past 50 years and then yearly for the past decade. Column A contains four-digit years, such as 1955, 1965, and so on. The default chart shown in the top of the figure does not create a date-based axis. You know this to be true because the distance from 1955 to 1965 is the same as the distance from 1995 to 1996.



(Excel does not recognize years as dates.)

There are two solutions to this problem:

1. Convert the years in column A to dates by using =YEAR(A2,12, 31). Format the resulting value with a yyyy custom number format. Excel then displays 2005 but actually stores the serial number for December 31, 2005.

2. Convert the horizontal axis to a date-based axis. Excel then thinks that your chart is plotting daily dates from May 8, 1905, through June 27, 1905. Because no date format has been applied to the cells, they show up as the serial numbers 1955 through 2005. Excel displays the chart properly, even though the settings show that the base units are days.