Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Plotting date & time (Excel 2002)

    I'd like to plot date/time data on an x-y chart. The y-axis is an interger value and the x-axis is time with the interval between data points determined from date/time values. I'd like the time scale on the x-axis to show the date/time of the associated y value. Is this possible in Excel 2002? If so, I'd appreciate some help in doing this.

  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

    Re: Plotting date & time (Excel 2002)

    Have the date and time together in a cell The date is the integer portion the time is the decimal portion.

    format as desired to show both date and time: "ddd, mmm d, hh:mm" to show as "Mon, Feb 9, 15:10" It should wrap as needed. If you want to force a text wrap at a certain point add a Linefeed at that point by holding the <ALT> key and pressing (no quotes): "0010" on the numeric keypad.

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Plotting date & time (Excel 2002)

    Thanks for the reply. If I understand your answer, I would input a date/time of February 9, 2004, 3:27 PM into a cell formatted for "general" as: 40209.1527 and then somehow reformat the value into date/time on the x-axis plot. I guess you can tell that my knowledge of Excel is somewhat limited.

  4. #4
    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

    Re: Plotting date & time (Excel 2002)

    Excel will convert for you dates/times entered into a cell you do not have to know the "serialdates"
    If you want to format to display like this:
    February 9, 2004, 3:27 PM
    Enter your dates and times into a cell (so it looks like a date and time). Excel will guess at a format. If once they entered it is not what you want, select the range and format - cells - number (tab) - Custom category, type:
    <pre>mmmm d, yyyy, h:mm am/pm</pre>


    When you make the chart it should read the format from the X and use it. If is does not, dbl-click the X-axis, and format like above:
    number (tab) - Custom category, type:
    <pre>mmmm d, yyyy, h:mm am/pm</pre>


    Steve

  5. #5
    New Lounger
    Join Date
    Feb 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Plotting date & time (Excel 2002)

    I'm still having difficulty.... The cells in the worksheet and the x-axis in the chart are now both custom formatted as mm/dd/yyyy, hh:mm:ss AM/PM. However, the chart does not distinguish between y-data taken at different times on the same date (the y-values for a given date are plotted at the same position on the x-axis without time taken into consideration). In addition, the labels on the x-axis pick up the date but not the time. The time is shown as 12:00:00 AM for all dates. I tried the "Help" in Excel and got the following: " Time-scale charts and times. You can't create time-scale charts from data that is measured at intervals of hours, minutes, or seconds. Only days, months, and years are considered base units in time-scale charts." Does this mean it can't be done?

    Edited Post: I finally have partial success. Each data point is plotted with a date and time label on the x-axis, but the distance between data points on the x-axis is all the same and not proportional to the time between the data points. I achieved the partial success by going to a custom format of mm/dd/yyyy, hh:mm on both the worksheet and the x-axis of the plot (24-hour clock). If you have any tips on how to get the time interval between data points to correspond to the input data, I would appreciate it. Thank you for your help up to now. At least I have something workable.

  6. #6
    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

    Re: Plotting date & time (Excel 2002)

    You must use an XY scatter. Most other charts use categories so are always equally spaced. If you want X-data to be values use the XY scatter.

    Steve

  7. #7
    New Lounger
    Join Date
    Feb 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Plotting date & time (Excel 2002)

    YES!! That did it! Thank you very much.

Posting Permissions

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