Microsoft Office Tutorials and References
In Depth Information
Converting Text Dates to Dates
Dates that are really time— It is not difficult to imagine charts in which the
horizontal axis contains periodic times throughout a day. For example, you might use a chart
like this to show the number of people entering a bank. For such a chart, you need a
time-based axis, but Excel will group all of the times from a single day into a single
point. See “Using a Workaround to Display a Time-Scale Axis” near Figure 3.19 for
the rather complex steps needed to plot data by periods smaller than a day.
Each of these problem situations is discussed in the following sections.
Converting Text Dates to Dates
If your cells contain text that looks like dates, the date-based axis will not work. The data in
Figure 3.8 came from a legacy computer system. Each date was imported as text instead of
as dates.
Figure 3.8
These dates are really
text, as indicated by the
apostrophe before the
date in the formula bar.
This is a frustrating problem because text dates look exactly like real dates. You may not
notice that they are text dates until you see that changing the axis to a date-based axis has
no effect on the axis spacing.
If you select a cell that looks like a date cell, look in the formula bar to see whether there
is an apostrophe before the date. If so, you know you have text dates (refer to Figure 3.8).
This is Excel’s arcane code to indicate that a date or number should be stored as text instead
of a number.
Understanding How Excel Stores Dates and Time
On a Windows PC, Excel stores dates as the number of days since January 1, 1900. For a
date such as 2/17/2011, Excel actually stores the value 40,591, but it formats the date to
show you a value such as 02/17/2011.
On a Mac running Mac OS, Excel stores the dates as the number of days since January 1,
1904. The original designers of the Mac OS were trying to squeeze the OS into 64K of
ROM. Because every byte mattered, it seemed unnecessary to add a couple lines of code to
handle the fact that 1900 is not a leap year. Excel for the Mac adopted the 1904 convention.
On a Mac, 2/17/2011 is stored as 39,129.
Search JabSto ::

Custom Search