+ Reply to Thread
Results 1 to 8 of 8

datetime interval with date axis

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    36

    Angry datetime interval with date axis

    Hi, What I thought would be very simple has proven to drive me nuts for the past 30mins I have no idea what i'm doing, data range, series, labels ahhhh.

    Below is my data, all I want is a 2D line graph with statvalue on the Y and plotted chronologically for the day across the X and have one Date Label on the X. Instead I am getting my five thousand stat values each having a label on the X! .

    BCDate ServerName StatName StatValue
    2016/03/29 00:00:14 server123 CPUPEAKP-TOTAL 44.76047904
    2016/03/29 00:00:29 server123 CPUPEAKP-TOTAL 15.58116232
    2016/03/29 00:00:44 server123 CPUPEAKP-TOTAL 32.44867301
    2016/03/29 00:00:59 server123 CPUPEAKP-TOTAL 23.21473631
    2016/03/29 00:01:14 server123 CPUPEAKP-TOTAL 33.83233533
    2016/03/29 00:01:29 server123 CPUPEAKP-TOTAL 14.8259454
    2016/03/29 00:01:44 server123 CPUPEAKP-TOTAL 34.23086512
    2016/03/29 00:01:59 server123 CPUPEAKP-TOTAL 19.53418482
    2016/03/29 00:02:14 server123 CPUPEAKP-TOTAL 30.06012024
    .....
    Last edited by am_hawk; 04-12-2016 at 03:16 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: datetime interval with date axis

    Are you date/times stored as text or as serial numbers? What chart type are you using?

    If I assume that your date/times are stored as serial numbers, I would suggest that you use an XY scatter chart (with connecting lines) rather than a regular line chart (which is what I assume you are using).

    If your date/time values are actually text strings, convert them to date/time serial numbers, then use them in an XY scatter chart.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: datetime interval with date axis

    Dates are stored and formatted as: yyyy/mm/dd hh:mm:ss Switched to scatter and now have x axxis 0,2,4,6,8,10 for the sample above. How would I get the x-axis label as just the date as now it is showing max/min major/minor? When I was usinng the 2d line and used just the date without the time so every row had the same date value I just got that one date under every single plotted point... I feel i am missing something very simple here...

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: datetime interval with date axis

    It might be something simple, but it is difficult to tell from a simple text description of you spreadsheet/chart. If you can upload a sample spreadsheet/chart illustrating the issues, we might be able to debug it faster.

    Dates are stored and formatted as: yyyy/mm/dd hh:mm:ss Switched to scatter and now have x axxis 0,2,4,6,8,10 for the sample above.
    These two elements make me think that the dates are stored as text strings and not real dates (the 1st date 2016/03/29 00:00:14 would be stored as the number 42458.0001620, for example). If you format the first column as "general", what do you see in those cells? An XY scatter chart interprets these text strings as simple counting numbers (1,2,3...), and you get a string of (meaningless) integers for the x values. In order to get the XY scatter plot to read the date/times correctly, they must be entered as date/time serial numbers. You might also check to make sure the 1st series of the XY scatter chart is using the 1st column as the X values.

  5. #5
    Registered User
    Join Date
    06-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: datetime interval with date axis

    Change the datetime to different formatting all charts look the same...Here is the upload
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: datetime interval with date axis

    Select the 1st XY scatter chart, and look at the first (only) data series. The x values are for three columns A2:C10, of which most of those values are text (This might be easiest to see in the Select Data dialog), and the Y values are for 1 collumn (D2:D10). So Excel sees the text in the x values and uses count numbers for the X values. Edit the X values for the data series so that it only uses the first column (A2:A10), and the chart should be right. Then format the x axis with the desired number/date/time format.

  7. #7
    Registered User
    Join Date
    06-04-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: datetime interval with date axis

    Thanks for the tips i see what you're saying, I also feel as though perhaps what i'm trying to accomplish does not logically make sense therefore is not achievable. My work around is to just not have any x axis labels and name the chart for the day I am displaying. But to take this one step further to really showcase the issue. If I have 3 days and ten data points per day, how do you only get the three days displayed once on the x axis? I'm looking for day 1 below the first data point, then 9 days plotted with no label, then under first plotted point of day 2 a label with day 2 and 9 points no label and again for the third. It still wants to put day 1 under all 10 points, then day 2 under the next 10 points...

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: datetime interval with date axis

    You should simply need to go into the "format axis" dialog for the x axis and adjust the min, max, major unit, and minor unit (if needed), until the axis appears as you want. A major unit of 1.0 should give you one major tick mark per day, which Excel should label with its value in the desired number/date/time format.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Datetime to date and time
    By mdek in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-08-2016, 03:40 PM
  2. if statement for datetime value between two other datetime values
    By OSepulvedaIII in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2016, 10:37 AM
  3. Horizontal Axis Interval Help
    By njuice8 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-12-2014, 03:58 AM
  4. Plotting unequal days interval in x axis
    By thong127 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-11-2013, 11:59 AM
  5. Axis Interval Values Displaced
    By jwcane in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-03-2012, 05:38 AM
  6. Based on a Column datetime value auto calculate and populate a datetime range
    By rajashanmuga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2010, 04:10 PM
  7. Replies: 2
    Last Post: 04-27-2009, 04:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1