General Question

Chatfe's avatar

Excel graphing of dates and times?

Asked by Chatfe (422 points ) May 31st, 2010

Excel formula question. How do I graph a series of different dates and times, overlaying them on a 24 hour graph that will show a scatter chart for each event? The dates/times are in the following format: “5/13/2010 23:45:14”, “5/14/2010 4:57:40”, “5/14/2010 10:11:09”. The point is to use the event history to show the busy times of day. Thanks.

Observing members: 0 Composing members: 0

10 Answers

lilikoi's avatar

The date and time formatting looks the same in all three cases. You can manipulate how the date is displayed using various date/time functions. I’m not getting what the problem is. Can’t you just plot time on the x axis and the corresponding data that changes with time on the y? Seems pretty straight forward.

jaytkay's avatar

Are you just graphing dates. I am picturing a 1-dimensional line, not a 2D scatter graph.

Chatfe's avatar

The problem seems to be that I can’t get them all to plot on a 24-hour period. In other words, I want to overlay all the dates/times over the same 24 period so I can see if more events happen in the morning or at night.

jaytkay's avatar

I might be catching on. Here is what I picture now. Separate the dates and times into separate columns.

05/13/2010 | 23:45:14
05/14/2010 | 04:57:40
05/14/2010 | 10:11:09

Graph it with date on the Y vertical axis
And X axis is the time

Your graph can be 24 hours wide, and as tall as necessary to fit all your dates.

Chatfe's avatar

Hmmm… Still not getting it. It looks like Excel doesn’t know how to handle time like this or I’m really missing something. I can’t get the times to overlay correctly and they are all evenly spaced on the graph, which means that they’re not displaying correctly.

lilikoi's avatar

Excel handles time perfectly fine.

Do NOT graph date on Y axis and time on X axis. That makes no sense at all.

Put date/time info in one column in chronological order.

You will have two columns. One for date/time the other for the data corresponding to time.

Simply click the “create chart” icon on the toolbar, select both columns (the whole data set) to graph. Make sure the X-axis is time and the Y-axis is the data that changes with time. If the dates and times are evenly spaced, check to make sure that each tick corresponds specifically to the times that are part of your data set. Odds are, they are not. You can set the number of ticks within a range on an axis, and probably Excel has automatically created evenly spaced tick marks and your data doesn’t fall exactly on any of these.

If that doesn’t make sense and you’re still stuck, post the data here and I will make your graph or upload the excel file somewhere that I can access.

Pretty sure I can solve your problem in a minute or two.

jaytkay's avatar

@lilikoi Do NOT graph date on Y axis and time on X axis. That makes no sense at all.

Yes, it makes sense. It is what a calendar program showing free/busy time over several days displays.

lilikoi's avatar

Okay, I get what you’re saying now. That would work if you were either busy or not busy at any given time. My brain just automatically assumed he had to quantify “busy” in further detail as in he had more than one thing happening at each time and I was thinking you’d have date:time on the x axis and some kind of scale of quantity of “busy-ness” on the y-axis. Now I have no idea what he is trying to do lol. Sorry for misunderstanding.

jaytkay's avatar

It’s a funny graph because the data that changes with time is “time”. Meaning time of day.

And I’m having difficulty, too, with Excel’s treatment of time. It wants to extend the time axis beyond 24 hours. It has to do with the fact that Excel doesn’t really have time data, everything is date/time, even if the date portion is not displayed.

@Chatfe Here are a couple of things to try.

1) Click on your Time axis, choose Format Axis, and on the Scale tab, set maximum to 1.
Which is one day.

2) Or change your time units to decimals. 4AM = 4.00, 7:30PM = 19.5, etc

Chatfe's avatar

Got it. I had to grab the dates using the Right formula, then rounded up to the hour. Seems like excel didn’t know what to do with minute, seconds etc. Then to make it easier to see volume I graphed events per hour on the Y axis. Thanks everyone.

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther