Graphing a Timeline Using Excel...

[excelsiors]

I want to graph a timeline using excel.
However, I don't have the data for every hours

The result is this:

I don't have the data for
1 AM => 3 AM
6 AM => 9 AM
12 PM & 1 PM
6 PM & 7 PM

However, I don't want excel to skip them like that. It's not logical.
I want it to insert missing hours and assume the value is where the line cut.

PS: The data above is compiled using a pivot table and I'm using excel 2010

 

[yearnintolearn]

You will have to make sure your x-axis labels have the 24-hr values and then you have to go in your gridlines. For all the values to display you'll click on your x-axis to bring up format axis, then go to interval between labels and specify the interval.

 

[Jon Peltier]

First, use real times in the chart's data:

0:00 9
4:00 7
5:00 9
10:00 3
11:00 3
etc.

Use an appropriate number format, like h AM/PM, so the data looks like:

12 AM 9
4 AM 7
5 AM 7
10 AM 3
11 AM 3

Don't make a line chart, because a line chart will only use those times as non-numeric labels.

Instead, insert an XY chart. You can format it with lines and no markers like your default line chart, though I think using markers is better, because it indicates where the data is and isn't.

To get tick spacing that aligns with hours, you need to figure out how much is an hour (1/24 = 0.04166667) and insert it into the format axis dialog. Otherwise Excel will use 1/10 or 1/5 of 24 hours for the major spacing.

 

[excelsiors]

I still can't graph it right:

I know it's not right because that's the same chart I get
when using the line graph with real-time value.

Here's the data:
Excel Data - Pastebin.com

I use a custom format to get it to display like that:
d"d" h:mm;"";"";""

I'm using excel 2010 and the interval is not 1/24 like you said it is.

 

[excelsiors]

When I first graph it, it comes out like this:

So yeah, it's a no go

 

[Jon Peltier]

1. Fix the Y values. They are properly numeric, but should not look like dates and times. For example, the first Y value looks like 1/2/1900 3:25:00 AM. Somehow your actual value of 2.14 has been displayed as a date and time. Excel considers dates to be whole numbers, the number of days since the turn of the 20th century (1/2/1900 is the date form of the whole number 2). Excel considers times to be fractions, the fraction of the day since midnight. 3:25:00 AM is time-speak for 0.14.

2. Check the X values (the times). Your chart is how an XY chart looks if either no X values are specified, or if even one X value is not numeric. Excel chokes, and instead of using the intended X values, it uses the counting numbers 1, 2, 3, etc. The points are equally spaced along the orizontal axis, not positioned according to the time value

Since every data point now falls on a whole number, and your number format is a time without a date, all you see is the time, which is the fractional part of the whole numbers, or 0:00.

3. Here is my reconstruction of your chart. X axis labels are all 0:00. I've added data markers in the second chart. There are too many close together to really see the spacing, so the third chart shows only the first 1/4 of the chart (I changed the axis maximum from the automatic 200 to 50).

In the following chart, I changed the number format from time only to date and time. The first date is January 0, 1900 (or zero in date format), the second is February 19, 1900 (which is 50). In the chart below that I've changed to a General number format, so we can see for sure Excel is not considering the times in the first column as times, but instead just that the axis is counting points from 1 to almost 200.

Equally spaced data points, axis labels only show 0:00, when viewed numerically the axis ranges from 0 to 200. Excel is not properly plotting the input times. The reasons for this include (a) no x values are actually specified, and (b) one or more of the x values are text rather than numeric.

4. The first chart below has good X and Y values. The shape of the curve is different, and the x axis has reasonable labels, not all 0:00. In the second chart below, I've added markers to show that the times are correctly interpreted, and the points are not equally spaced. In the third chart, I've changed the x axis to numeric format, and we can see values ranging from 0.000 to 1.000. This is how it should look.

 

[excelsiors]

Thanks for the detailed answer.

I see the problem with my chart.
The original data is like this:

[TABLE="width: 243"]
<tbody>[TR]
[TD]8/8/2013 3:16 PM[/TD]
[TD]1/0/1900 2:40 PM[/TD]
[/TR]
[TR]
[TD]8/8/2013 3:27 PM[/TD]
[TD]1/0/1900 2:29 PM[/TD]
[/TR]
[TR]
[TD]8/8/2013 3:29 PM[/TD]
[TD]1/0/1900 2:29 PM[/TD]
[/TR]
[TR]
[TD]8/8/2013 3:32 PM[/TD]
[TD]1/0/1900 2:26 PM[/TD]
[/TR]
[TR]
[TD]8/21/2013 3:35 PM[/TD]
[TD]1/0/1900 10:44 AM[/TD]
[/TR]
[TR]
[TD]8/21/2013 3:37 PM[/TD]
[TD]1/0/1900 10:45 AM[/TD]
[/TR]
</tbody>[/TABLE]

I wanted to day-part the data so I trimmed it using this function:
=text(A2,"hh:mm")

Unfortunately, the data comes out as text.

Is there a better way to trim the hour?
I can't use txt to column as it'll leave out the PM part.

Or better yet, is there a better way to day-part
the data directly from the pivot table?

I still can't get the same chart you're getting though with
the correct X axis format

Also, how do you display that neat grid in your chart?

 

[Jon Peltier]

=text(A2,"hh:mm")
This puts text into the cells (not numeric times), which Excel can't plot, so that's why it uses 1,2,3,...

Try
=A2-INT(A2)
Which means Date Plus Time minus Date
Format as time.

The neat grid is simply the default Excel 2013 formatting for a scatter plot.