# Excel graphs and time values

I have a table of times I went to sleep at different dates. It looks like this:

Now, when I graph the data with the line chart, Excel thinks that 23:44:00 is after (later than) 00:56, so it graphs the 23:44 as happening after 00:56 - displaying 23:44 higher vertically than 00:56.

So my question is this: How do I make it display time values lower than 00:00:00 as happening before that time?

• Excel is just graphing as an integer so add a formula to your data to convert the integer to what makes sense Commented Mar 8, 2017 at 16:56
• @EricF Could you elaborate, please? What type of formula? Commented Mar 8, 2017 at 16:57
• Well in your case you basically want 23:44:00 to appear like -00:16:00 right? That would make it appear to go down in the graph Commented Mar 8, 2017 at 16:58
• @EricF Yes, 16 minutes to 00:00:00 - that's how I want it to appear on the graph. How do I do that? Commented Mar 8, 2017 at 17:00
• So in your cells put something like =IF(value >= 12, value = value - 12,value) where value is the time you are referencing Commented Mar 8, 2017 at 17:03

This is a classic Excel issue with how Dates and Times are stored and calculated. Dates are stored as serial numbers (integers), from Microsoft themselves:

Excel stores Dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

And

Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day)

So, since you're looking to compare times, you need to think in terms of what "day" your time falls on. Since Excel considers midnight the start of a new day, you need to account for that in your calculation. So your 23:44 time is really occurring at the end of a day, while 00:56 is at the start of a day.

To properly chart this, you'll need a "helper column" that translates times to essentially before or after midnight-giving them the correct relation to midnight. So, in your helper column, add 1 (day) to times before noon, and add 0 (days) to times afternoon.

`=IF(sleep_time < 0.5, sleep_time+1,sleep_time)`

It may help to change your number format to `Number` to see how Excel is interpreting your times.