Dates in Chart Based on Linked Excel Worsheet Data

[stevecroyle]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a spreadsheet set up to record weekly data. In another spreadsheet, I have charts set up based on the data in the first spreadsheet. My charts seem to work just fine, with the exception that the dates, which form my X axis and are linked from the first spreadsheet, are increased by exactly four years on the chart.

Tried adjusting the X-axis formatting, but nothing worked. When I changed the minumum and maximum scale settings to be the original range, I got prompted to make sure my dates were within the appropriate range, so I had to cancel out of the dialog.

Any ideas, either for what's going on or for a solution?

 

[NickZ]

Same problem. Frustrating.

 

[CyberTaz]

Can you clarify a bit? Is the data in the same file as the charts (different
worksheets in the same workbook) or are the charts in one workbook linked to
data in a different workbook?

Please be as descriptive of what you're working with as you possibly can.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Oscar]

Same problem:

X axis are dates. Every month for 4 years.
Instead of showing every month, I would like to show the scale in quarters.

When I attempt to adjust *any* parameter on the scale options I get the message: "The entry is invalid for the data used by this chart"

The type of data is set to date. The data and chart live in the same worksheet/file.

Is this a bug? Any workarounds?

 

[CyberTaz]

Sorry Oscar but this is *not* the same problem The issue in this thread
is in regard to dates plotting 4 years off. A reference to changing the
scale for date values was made by another poster who merely mentioned having
tried that in order to resolve the original problem. Grouping date data by
period is totally unrelated.

In order for anyone to help out you should post a NEW message rather than a
REPLY to another thread. Include which specific version of Excel & OS X
you're using as well as a complete description of exactly how your data is
organized, how you're going about it & what you want to be able to do.

Mixing issues within a thread makes it difficult for everyone - including
yourself - to follow the messages & maintain focus on the original issue.

Thanks

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[stevecroyle]

Here's what I have. One Excel file contains the data. A separate Excel file contains the charts. (The reason for doing it this way, rather than having them together in the same file, is so that I can sync the file with a Palm PDA. I used to have a single file, but the charts wouldn't sync correctly and the chart formatting was corrupted if I saved the file while editing it on my PDA).

The data I'm pulling for the chart in question is listed in just two columns - date for the first column and attendance for the second. For example: for 11/19/2008 (first column) there were 65 students present (second column). The list is continuous for every Wednesday from 9/1/2004 to the present.

The chart that's giving me fits with the dates displays all the attendance data correctly, including a trendline. The dates, however, in the X-axis of the chart are listed starting from left to right 9/2/08, 10/2/08, 11/2/08, 12/2/08, 1/2/09, 2/2/09, and so forth to 12/2/12. According to the data to which they are linked, the dates should start from 9/1/04, 9/8/04, 9/15/04, 9/22/04, 9/29/04, and so forth to 12/31/08 and beyond.

Just to clarify, the dates in the Excel data file remains unchanged by the chart data. The correct dates are in this data file.

The Format Axis Scale dialog is set as follows:
Horizontal (Category) axis type: Date
Horizontal (Time) axis scale
Minimum: Auto
Maximum: Auto
Base unit: Auto
Major unit: Auto
Minor axis: Auto
Vertical Axis crosses at: Auto
Dates in Reverse Order: Unchecked
Vertical (Value) axis crosses between dates: Checked
Vertical (Value) axis crosses at maximum value: Unchecked

I have tried tinkering with each of these settings, but none help. The attendance data is locked to 4 years ahead.

Let me know what else you need to know about the issue.

Thanks for your help.

 

[stevecroyle]

I think, perhaps, I have some clues that may be useful in figuring this out. I just created a test data file and a separate test chart file that mimics the setup of the original files. I entered three years of dummy data in the data file and set up a linked chart in the separate dummy chart file. The dates in the X-axis chart correctly.

How does this help? The original files were created in and include the original data from when I was using the Windows version of Excel (2003). The dummy files are obviously created and linked using only Excel 2008 for Mac.

Also, on the original chart file, when I go into the Format Axis Number dialog and change the Category to General or Number, the X-Axis dates change to a number format starting at 38231, 38261, 38292, 38322, 38353, and so forth to 39783. Now when I go to the original data file and change the format for the date column from date to number, the dates change to numbers starting from the same 38231. This seems to suggest that the correct values are being applied to the chart, but that the failure is taking place when translating those values to the correct dates.

Of course, this didn't happen in my dummy setup created solely in Excel 2008. Could this, perhaps, indicate a compatibility issue when translating date values created in Excel 2003 to date values created in Excel 2008?

 

[CyberTaz]

Thanks for all the explicit detail, but in this case the answer is just as I
suspected to begin with The Date System of the file the data is in is
set to 1900 (typically used by Windows-compatible systems) & the file your
charts are in is set to the default 1904 Date System used on a Mac.

In your file of charts go to Excel> Preferences> Calculation & remove the
check for the 1904 Data System then save the file. If you're linked to the
data refresh the link or import it again to update the charts.

The setting is workbook specific, so you don't normally have any reason to
notice which date system is used by the file - regardless of which OS you
open the file on the dates will be correct. However, when you copy/paste
from one ate system file to another or link between disparate files your
dates will be ~4 years off (± depending on which way the data goes).

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[stevecroyle]

That did it. Thank you so much. I knew it had to be something simple.

I really appreciate your help.

 

[Not liking Excel for Mac]

I'm having the same problem, although I have removed the check for the 1904 date system. Whenever I try to format the axis and change the scale to date, I immediately get the error statement. "The entry is invalid for the data used by this chart. Please enter a value that falls between the ...." I cannot get around it and it is driving me nuts.