Using a Date-Based Axis with Dates Before 1900 Spanning Less Than 100 Years in Excel 2007:-

In the Figure (given below), the dates in column A are text dates from the 1800s. Excel cannot automatically deal with dates from the 1800s, but it can deal with dates from the 1900s.



(Transforming the 1800s dates to 1900s dates and using clever formatting allows Excel to plot this data with a date axis.)

One solution is to transform the dates to be dates in the valid range of dates that Excel can recognize. You can use a date format with two years and a good title on the chart to explain that the dates are from the 1800s.

To create the chart in the above Figure, you follow these steps:

1. Insert a blank column B to hold the transformed dates.

2. Enter the formula =DATE(100+RIGHT(A4,4),LEFT(A4,2),MID(A4,4,2)) in cell B4. This formula converts the 1836 date to a 1936 date.

3. Select cell B4. Press Ctrl+1 to open the Format Cells dialog. Choose the date format 3/14/01 from the Date category on the Number tab. This formats the 1936 date as 6/15/36. (You will later add a title to indicate that the dates are in the 1800s.)

4. Double-click the fill handle in cell B4 to copy the formula down to all cells.

5. Select the range B3:C17.

6. From the Insert ribbon, choose Charts, Line, 2-D Line, Line.

7. From the Layout ribbon, choose Legend, No Legend.

8. Right-click the vertical axis along the left side of the chart and choose Format Axis from the context menu.

9. In the Format Axis dialog that appears, on the Axis Options page, choose the Fixed option button next to Minimum and enter a fixed value of 20.

10. Without closing the Format Axis dialog, click the dates in the horizontal axis in the chart. Excel automatically switches to formatting the horizontal axis, and the settings in the Format Axis dialog redraw to show the settings for the horizontal axis. In the Axis Type section, choose Date Axis. Click Close to close the dialog box.

11. From the Layout ribbon, choose Chart Title, Centered Overlay Title.

12. Click the State Count title. Type the new title Westward Expansion<enter>During 1845-1875 Added 13<enter>New States to the Union. Click outside the title to exit Text Edit mode.

13. Click the title once. You should have a solid selection rectangle around the title. On the Home ribbon, click the Decrease Font Size button. Click the Left Align button.

14. Carefully click the border of the title. Drag it so it appears in the top-left corner of the chart.

15. Select the dates in B4:B17. Press Ctrl+1 to access the Format Cells dialog. On the Number tab, click the Custom category. Type the custom number format ‘yy. This changes the values shown along the horizontal axis from m/d/yy format to show a two digit year preceded by an apostrophe.

The result is the chart shown in the above Figure. The reader may believe that the chart is showing dates in the 1800s, but Excel is actually showing dates in the 1900s. This method fails when you are trying to display more than 100 years of data points.