Microsoft Office Tutorials and References
In Depth Information
Plotting Data by Numeric Year
1. Because the year is always the two rightmost characters in column A, enter the formula
=RIGHT(A2,2) in cell B2.
2. Because the month is the leftmost one or two characters in column A, ask Excel
to find the first slash and then return the characters to the left of the slash. Enter
=FIND(“/”,A2) to indicate that the slash is in second character position. Use
=LEFT(A2,FIND(“/”,A2) to get the proper month number.
3. For the day, either choose to extract the first or last date of the range. To extract the
first date, ask for the middle characters, starting one position after the slash. The
logic to figure out whether you need one or two characters is a bit more complicated.
Find the position of the dash, subtract the position of the slash, and then subtract 1.
Therefore, use this formula in cell D2:
=MID(A2,FIND(“/”,A2)+1,FIND(“-”,A2)-FIND(“/”,A2)-1)
4. Use the DATE function as follows in cell E2 to produce an actual date:
=DATE(B2,C2,D2)
3
Plotting Data by Numeric Year
If you are plotting data where the only identifier is a numeric year, Excel does not
automatically recognize this field as a date field.
For example, in Figure 3.15 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 1960, 1970, 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 1960 to 1970 is the same as the distance
from 2000 to 2001.
Listed here are two solutions to this problem:
Convert the years in column A to dates by using =DATE (A2,12,31) . Format the
resulting value with a yyyy custom number format. Excel displays 2005 but actually
stores the serial number for December 31, 2005.
Convert the horizontal axis to a date-based axis. Excel thinks your chart is plotting
daily dates from May 13, 1905, through July 2, 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.
Using Dates Before 1900
In Excel 2010, dates from January 1, 1900 through December 31, 9999 are recognized
as valid dates. However, if your company was founded more than a demisesquicentennial
before Microsoft was founded, you will potentially have company history going back before
1900.
Figure 3.16 shows a dataset stretching from 1787 through 1959. The accompanying chart
would lead the reader to believe that the number of states in the United States grew

Search JabSto ::

Custom Search