Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    x-axis format problem: How to label the first date and ignore the rest of the day

    Dear All,

    I have a problem plotting data with x-axis. The x-data consists of date with time but exists for some time of a day only. When I plot x-data with y-data, you can see discontinuous data as shown in the attachment (top figure). If I index the x-axis with ordered number ie, 1, 2 ,3, I get continuous data but x-data could not show the date. The ideal chart is shown in figure below, continuous data with date label on the x-axis (ie, the start of the day will be shown for all available days only).

    Any suggestions?

    Thank you for your help
    Leo
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The data you have has gaps in it leading to the discontinuity. Most are 5 min apart but some are 10 or 20 min, but the real issue comes from the missing data that is hours apart, giving you big gaps. Plotting vs record number ignores the differences in the gaps, but is misleading in other ways, suggesting the data is continuous and regularly taken

    Here are 3 possibilities. The top still has the "discontinuity" but displays the dates (you could also include some times if desired)
    The middle adds a range to plot the first data with datalabels for the dates. I did not put in the 7/30 since it was too close to the 7/31. (this is similar to your "request")

    The bottom option would be to "lie" to excel about the time and "convert" the "8 hours" to 24 hours so instead of being 5 min apart , the time is space would be more like 15 min. (I would not plot time since None are accurate). I used 8:15 for "Midnight" and 12:30 as "Noon" (you can adjust) and used trend to calculate the converted time which puts the min of the time (8:45) as "1:24" and the Max time (16:30) at 23:17.

    Steve

    Steve
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Oct 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear Steve,

    It is awesome.

    "Most are 5 min apart but some are 10 or 20 min"
    We would make them all 5 minutes apart. Sorry about it. Each day will begin 0845 and end at 1630.

    "Plotting vs record number ignores the differences in the gaps, but is misleading in other ways, suggesting the data is continuous and regularly taken"
    We know the data is discontinuous because we are interested in the activities in the day only. The bottom one looks great however, by transforming the 8 hours to 24 hours, I noticed the graph has changed slightly. ie some parts are elongated. It is not good for us.

    For the middle one, it is perfect, but we have to keep moving the labels when we change the time frame of the data, it is not good.

    Are there any ways to keep the middle plot but with the x-axis following the bottom plot or the labels can stitched to the corresponding data?

    Thank you very much for your teachings
    Leo

Tags for this Thread

Posting Permissions

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