Date Problems
 

Dear Microsoft:
Since we all know that the cost of a 1st class stamp is going to go up, I
thought I would plot the cost over time.
http://www.nonprofitmailers.org/tools/ratehist.htm

1. I formatted a column as DATE, but the 3 dates prior to 1900 that I
entered as x-x-xxxx were not recognized by Excel as dates. (??!!???!). When
I reentered them as x/x/xxxx Excel recogized July 1, 1885, but not the other
2. (Huh?)

2. So then I charted the dates and the costs as a line chart, the costs are
displayed ok. The dates are displayed on the x-axis, but the dates are
equidistant. That's not what I had in mind. And when I tried an x-y chart,
it got even more pitiful. (I'd tell you how pitiful, except this "New
Question" window isn't a window, it's commaneered my entire screen . . . )

Really, this is easy. Or am I missing something?

Thanks for your help,
Mike

Mike,

By design Excel uses two date systems - a 1900 system and a 1904 system
(look up "date" in Excel's help for more information). As a result, you're
not going to be able to format dates prior to 1900 as "date".

Maybe it defeats the purpose, but since there are only three data points
below 1900 could you modify your chart to show dates for 1900 forward? As
you stated a line chart would be more desirable because it implies constant
growth over time. However, if 1900 forward is not an option then maybe you
could format the dates as text and use a clustered column chart in place of a
line chart. -- Just a suggestion.

--
Regards,
John Mansfield
http://www.pdbook.com


"MikePiehl" wrote:

Dear Microsoft:
Since we all know that the cost of a 1st class stamp is going to go up, I
thought I would plot the cost over time.
http://www.nonprofitmailers.org/tools/ratehist.htm

1. I formatted a column as DATE, but the 3 dates prior to 1900 that I
entered as x-x-xxxx were not recognized by Excel as dates. (??!!???!). When
I reentered them as x/x/xxxx Excel recogized July 1, 1885, but not the other
2. (Huh?)

2. So then I charted the dates and the costs as a line chart, the costs are
displayed ok. The dates are displayed on the x-axis, but the dates are
equidistant. That's not what I had in mind. And when I tried an x-y chart,
it got even more pitiful. (I'd tell you how pitiful, except this "New
Question" window isn't a window, it's commaneered my entire screen . . . )

Really, this is easy. Or am I missing something?

Thanks for your help,
Mike

Thanks John, I got rid of the dates from the 1800's and now the chart looks
fine.
Thanks for your help.
Mike

"John Mansfield" wrote:

Mike,

By design Excel uses two date systems - a 1900 system and a 1904 system
(look up "date" in Excel's help for more information). As a result, you're
not going to be able to format dates prior to 1900 as "date".

Maybe it defeats the purpose, but since there are only three data points
below 1900 could you modify your chart to show dates for 1900 forward? As
you stated a line chart would be more desirable because it implies constant
growth over time. However, if 1900 forward is not an option then maybe you
could format the dates as text and use a clustered column chart in place of a
line chart. -- Just a suggestion.

--
Regards,
John Mansfield
http://www.pdbook.com


"MikePiehl" wrote:

Dear Microsoft:
Since we all know that the cost of a 1st class stamp is going to go up, I
thought I would plot the cost over time.
http://www.nonprofitmailers.org/tools/ratehist.htm

1. I formatted a column as DATE, but the 3 dates prior to 1900 that I
entered as x-x-xxxx were not recognized by Excel as dates. (??!!???!). When
I reentered them as x/x/xxxx Excel recogized July 1, 1885, but not the other
2. (Huh?)

2. So then I charted the dates and the costs as a line chart, the costs are
displayed ok. The dates are displayed on the x-axis, but the dates are
equidistant. That's not what I had in mind. And when I tried an x-y chart,
it got even more pitiful. (I'd tell you how pitiful, except this "New
Question" window isn't a window, it's commaneered my entire screen . . . )

Really, this is easy. Or am I missing something?

Thanks for your help,
Mike