Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 17 '13 at 18:16

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 0 down vote accepted

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.