Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with date calculations (bank hols etc) | Excel Discussion (Misc queries) | |||
time and date problems still | Excel Worksheet Functions | |||
Date sort problems | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |