1

I have a table of times I went to sleep at different dates. It looks like this: The date is formatted as dd.mm.yyyy and the time hh:mm:ss

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.

Here Excel charts the 23:44 as shown. I'd like it to understand that 23:44 is earlier 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?

7
  • Excel is just graphing as an integer so add a formula to your data to convert the integer to what makes sense
    – Eric F
    Mar 8, 2017 at 16:56
  • @EricF Could you elaborate, please? What type of formula?
    – Akres
    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
    – Eric F
    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?
    – Akres
    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
    – Eric F
    Mar 8, 2017 at 17:03

1 Answer 1

2

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)

Then, chart your helper column.

time_across_midnight

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

times as numbers

1
  • 1
    Thank you so much, @dav. This is exactly what I was looking for :)!
    – Akres
    Mar 9, 2017 at 13:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .