?
Solved

Excel Chart Timeline has wrong dates

Posted on 2011-10-24
7
Medium Priority
?
1,063 Views
Last Modified: 2012-05-12
OK what am I doing wrong here? This should show 10 days worth of data, and the ttrend does, but the dates on the bottom are all wrong.
 Chart with wrong timeline
0
Comment
Question by:Salad-Dodger
  • 4
  • 3
7 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 37019322
Please use a scatter chart when plotting data every 5 minutes. The line chart is really only suitable when your data is daily, monthly or yearly (in Excel 2003).

We could give more specific suggestion if you post the actual Excel workbook rather than a screenshot.
0
 
LVL 1

Author Comment

by:Salad-Dodger
ID: 37019516
When converted to a scatter chart, the timeline now runs from 0 to 2000, Set the format to date and it runs from 1/1/1900 to 6/22/1905, Its like it isn't tied to the date column

Here is the sheet Excel-Test-Sheet.xls
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 37019608
I changed the bottom chart from line chart to scatter chart. I changed both charts to use column F as the X-values. Column F contains the formula:
=SUM(A2,B2)                         copied down and formatted as mmm d, yyyy h:mm AM/PM

You are correct in your assessment that your original chart wasn't tied to the date column. I fixed that by editing the series formulas (click on a datapoint on the chart to see the series formula).


Excel-Test-SheetQ27412431.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 81

Expert Comment

by:byundt
ID: 37019804
If you click on a datapoint in the bottom chart (which I didn't change), you will see a series formula like:
=SERIES('GRPS_Test- GRPS EFF'!$E$1,'GRPS_Test- GRPS EFF'!$A$1:$E$1,'GRPS_Test- GRPS EFF'!$E$2:$E$1730,2)

I edited the second term to force it to use the date/time data in column F:
=SERIES('GRPS_Test- GRPS EFF'!$E$1,'GRPS_Test- GRPS EFF'!$F$2:$F$1730,'GRPS_Test- GRPS EFF'!$E$2:$E$1730,2)

Repeat as required for the other two series on that chart. Note that the bottom-most series has a series formula with two successive commas. The column F date/time cell references go between those two commas:
=SERIES('GRPS_Test- GRPS EFF'!$C$1,,'GRPS_Test- GRPS EFF'!$C$2:$C$1730,3)             initial series formula
=SERIES('GRPS_Test- GRPS EFF'!$C$1,'GRPS_Test- GRPS EFF'!$F$2:$F$1730,'GRPS_Test- GRPS EFF'!$C$2:$C$1730,3)        final series formula

0
 
LVL 1

Author Comment

by:Salad-Dodger
ID: 37019820
This is odd. you shouldn't be able to use SUM with Date and Time to get a concatenated value yet it does seem to work. Must be Excel hiding the real value in that cell. Since this data came from a csv, how on earth does Excel know how to treat those values differently in a SUM than it did in the chart function?

If I format Col A2 and B2 as Date and Time SUM does not give the proper value. This is not intuitive.

Further, I see that scatter plot gives a day buffer before and after, as well as some above and below. Can this buffer be removed? I don't need a blank day before and after
0
 
LVL 81

Expert Comment

by:byundt
ID: 37019924
You never specified the dates or times in your original chart--just the Y data. Instead, you created a line chart with the major unit being days. That's why Excel invented an erroneous series of dates to match your data.

Excel stores dates as whole numbers that increase by one with each passing day. A value of 0 represents January 0, 1900. I realize that there is no zeroth day of the month, but this trick allowed Excel to deal with 1900 not being a Leap Year--and the practice was consistent with Lotus 123.

Excel stores times as fractional parts of a day. So 0.5 would be 12:00 noon and 0.75 would be 6:00 PM.

By adding the date in column A to the time in column B, Excel creates a floating point number that represents both date and time. And it will display correctly if you format the cell appropriately.

When I opened your spreadsheet in Excel 2003, column A was already formatted as Date and column B as time. Adding the two numbers together gives you a result like 40829.00347, which you can reformat as Oct 13, 2011 12:05 AM.

If you want to eliminate the one day buffer around starting and ending dates for the chart, right-click the X-axis and choose Format...Axis from the pop-up. Go to the Scale tab and specify a minimum value 1 higher than it is now and a maximum value 1 less.
0
 
LVL 1

Author Closing Comment

by:Salad-Dodger
ID: 37020310
Thank you for the detail in your response. I'm still convinced there's a little voodoo in Excel !
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question