• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

6. Hacking Formulas and Functions > 74. Display Negative Time Values

Display Negative Time Values

Sometimes you want to display negative time values, but Excel will only display ######. There are several ways to escape this problem.

If you enter the time 12::00::00 (midday) into any cell and then format it as General, you will see that it has a numeric value of 0.5. Similarly, enter the time 24::00::00 in any cell. Look in the Formula bar and you will see that Excel shows 1/1/1900 12::00::00 AM.

Excel sees dates and times as nothing more than numbers. In the case of a date, by default Excel considers 1 January 1900 to have a numeric value of 1; 2 January 1900 a numeric value of 2; and so forth. Times are seen as decimals, with the exception of midnight, which has a numeric value of 1. (See


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint