Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have data that was collected over 4 days, with the times being recorded hh:mm:ss. However, since it was over multiple days, the same time, for example 14:58:30 repeats 4 times. So when I try to graph it, the data points for my y-axis for the different days (i.e. Mon, Tues, Wed, Thurs) all graph together wherever 14:58:30 happens to be on the x-axis. How do I make sure the data (ex: 2) for a given timepoint (ex:14:58:30) on Monday does not graph at the same x-axis timepoint (i.e. 14:58:30) for the Tuesday data (ex:3).

The way I know the different days, is because I know what day my instrument started collecting data (i.e. Monday) and when it stopped (Thursday). So I visually marked Day 1 as the first 24 hour pass, the 2nd day as the next 24 hour pass etc.

Any input appreciated

share|improve this question
    
I added details to my answer following your comment. –  mtone Jan 23 '11 at 23:50

2 Answers 2

You can add a date and a time together by adding a simple formula in a new column: =A1 + A2 (date + time). Then use this value to make your charts.

Just make sure your date and time columns are in proper excel format (eg. 23/01/2011, not just "Tuesday").

This works because internally, excel represents a date as a integer, and time of the day as a fraction. Today's date + 0.5 = Today at noon.


Working with charts and intraday data

  • Always use Scatter XY charts. Other chart types will either use midnight or the number of entries (depending if date format is used or not) as the base unit. Scatter XY will place the spots correctly within the day.
  • Unlike other chart types, scatter does not autodetect the date format, it uses the internal values (23/01/2011 = 40566). To find out the value of a day, simply change the format of a cell from Date to Integer. You can write down these values as start and end point of the axis.
  • To have gridline units by the hour rather than by the day, you need to have a base unit less than 1. One 24th of a day is 0.04167. Put this number in Major Unit and set the Number Format to Time (you can remove the seconds by using a custom format such as hh:mm AM/PM).
  • To display a chart from 8am to 4pm for one day, you can use the technique aboves and set your start and end points to 40566.33334 and 40566.66666 (1/24 *8 and *16)

References: Microsoft Office Blog, Excel 2007 Charts Book, Excel Online Help

share|improve this answer
    
Thanks so much mtone! Got it to work. However, how can I isolate a portion of time for the graph to focus on. That is when I try and concentrate my scale to focus on a time period, it seems I have to guess at random #'s that represent my time. –  Rae Jan 23 '11 at 21:30

To narrow down the time period, select the x axis and choose format axis.

You can then adjust the maximum and minimum points on the scale, as well as the intervals between check marks and labels

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.