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