Plotting time vs. date

[sashak]

Hello,

I am wondering if someone can help me plot a graph of i.e. time i go to bed. I have no problems plotting any other values and adding trend lines etc. But, with time, it is much harder as excel doesnt seem to respond appropriately to the day change.

I tried to plot it myself - here is my sample data, and my attempt to plot it
http://cid-bf7398e8f9f92dc5.skydrive.live.com/self.aspx/Public/timegoingtobed.xlsx

As you can see, even though the time series are i.e. 1am, 12:30 am, 11:30 PM, 1,am, 1am, there is a HUGE dip in the graph.

Can anyone advise what am I doing wrong, or is this just a flaw in excel

thanks in advance

sasha

 

[jeffreybrown]

Hi Sashak & Welcome to the Board,

If your vertical axis are the times and your horizontal axis are the dates, right click on the horizontal axis and select format axis and then adjust the axis type to text. The will close the gaps as you move from 10/29 to 11/1.

 

[gnrboyd]

I did not open your spreadsheet but I think I know what your issue may be without doing so. Times can be a little confusing to work with in Excel. I'm certainly no expert but I have one option that might work for you.

First of all, you should understand how Excel treats dates. Each day is a number beginning with January 1, 1900 which is the number 1. Each day after adds 1. For example, today is 40,131. Tomorrow is 40,132 and so on. So, 40,131.5 would be today at noon. Get it?

Your problem is that you are graphing times without dates and Excel views them from 0 to 1. 6 AM = .25. 12 PM = .5, 6 PM = .75. So when you go to bed after midnight, Excel sees this as a number just a little greater than zero. When you go to bed just before midnight, Excel sees this as a number just a little less than 1. This is why you are seeing dips in your graph. 11:55 PM one night and 12:05 AM the next night would be a sharp dip when you actually want it almost flat in your graph. (At least this is what I think you are explaining as your problem.)

This may not be the best solution, but it should work for you. (If you have odd sleep times you might need to adjust it some.) You need to factor in a date to trick Excel. In this case, the actual date doesn't matter. We just need to add 1 to any time greater than or equal to midnight to make Excel think this is greater than a time before midnight.

My solution would involve one column for you to key in your times. Another column would have a formula referencing each of those times to convert it. The formula is a simple if, then, else formula. If your time is<.5 (or before noon) it adds 1 + your time. Otherwise, it gives the same result as the date entered. For 3 AM, the result would be 1.125. For 9 PM, the result would be .875. (The .5 / noon time could be changed to reflect your actual sleep habits.)

You will then plot your graph based upon the column with the adjusted formulas. You also should format these cells as a time so the graph will look like it should. (right click, format cells, number tab, select time under category and select the option you want to view.)

The HTML program I have doesn't copy graphs but here is the spreadsheet showing the formulas you need to graph. (Column B) I added column C just so you could see the actual values that are being graphed. Hope this makes sense and you find it helpful. Good luck.

Book4
	A	B	C	D
1	Time	Time	ActualNumber
2	9:00PM	9:00PM	0.875
3	10:00PM	10:00PM	0.917
4	11:00PM	11:00PM	0.958
5	12:00AM	12:00AM	1.000
6	10:00PM	10:00PM	0.917
7	12:00AM	12:00AM	1.000
8	1:00AM	1:00AM	1.042
9	3:00AM	3:00AM	1.125
Sheet1

 

[sashak]

Thanks jrb and gnrboud,

@jrb - I tried formatting as you mentioned and unfortunately it didnt help

@gnboud - I think you are right about the problem I have - basically the eline shold be fairly flat. However, even when I plot is as you suggested, with the formla etc, the graph still comes out pretty much the same way as it did before I did the changes. The problem is when a 'going to sleep time' goes above midnight - excel is not able to interpret that small time change of 30 min properly.

thanks guys******** type=text/javascript> vbmenu_register("postmenu_2122260", true); *********>

 

[gnrboyd]

If you have followed my example and plotted column B and not A, the times after midnight are greater than those less than midnight and should plot as such leaving your graph the way you want. I did it as a test last night and it worked properly. Are you sure you are plotting column B and have implemented the formulas?