Microsoft Excel 2007 – How to Display Negative Time Labels on Chart

microsoft excel

Negative time labels are displayed in this Microsoft Excel 2003 file:

Graph in Excel 2003 shows negative time labels

The same file opened with Excel 2007 does not display negative time labels:

Graph in Excel 2007 does not show negative time labels

The axis number format and the source data format is the same: [hh]:mm.

The "Calendar from 1904" option is set in both version.

Using the standard number format, negative labels are displayed:

enter image description here

How can I make Excel 2007 do better?

Best Answer

Here's how I answered my own question:

  • Added a helper column with cumulated time as hours, formatted as a standard number:

    Date             | Accumulated time  | Accumulated time for the graphic
    August 13, 2007  | -00:03            | -0,05
    August 14, 2007  |  00:02            |  0,033333333
    August 16, 2007  |  00:29            |  0,483333333

    The formula for the helper column being AccumulatedTime * 24 so 1 means one hour.

    Keep in mind the workbook must have the Calendar from 1904 option set for negative time to be properly displayed. Otherwise, they'll show as a long string of #.

  • Changed the source data column in the graphic.

  • Set axis number format as 0":00". It works nicely if the axis unit is an hour or multiple hours, but not a fraction of an hour.

The solution is quite simple once I think of it, if not straightforward. Hope this helps other people too!

Related Question