Time (over 4 days) versus data - how to make the graph in Excel

Question

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

Answer 1

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

Answer 2

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