excel plot against a date time x series [closed]

Question

Why does Excel ignore the time component of datetime values in a chart. For example, the following data :

10/10/2010 9:30   1  10/10/2010 9:40   2 10/10/2010 9:50   3 10/10/2010 10:00  4  10/10/2010 10:10  5 10/10/2010 10:20  6 

excel plots it all values on a same x-value 10/10/2010. If I format the values as time, it works fine. How do I tell excel to display datetime and interpret it as datetime not date..

Answer 1

Try using an X-Y Scatter graph with datetime formatted as YYYY-MM-DD HH:MM.

This provides a reasonable graph for me (using Excel 2010).

Answer 2

I know this is an old question, but I was struggling to do this with a time period over 2 hours, so this might help someone. Also, several of the answers don't actually plot against time, giving equal space whatever the duration.

Firstly, as @jameswarren says, use a scatter graph. Then right-click the horizontal axis and choose Format Axis.

Under Number, select Time, and at this point you may find your scale goes a bit crazy, because it chooses to scale the axis by days. So go back to Axis Options and select Fixed for the Minimum, Maximum and Major unit scales.

To set the unit to hours, type in 1/24 = 0.0416667 (I used half that to get half-hourly increments). To make this start at a round number, multiply it by your preferred number of hours and type that into the Minimum box. In my case 08:00 = 0.333333