Two Excel Time Format Tips
Two questions I am often asked about the time format in Excel are: Why can’t it show times greater than 24 hours? and Why can’t it show negative time?
To help understand how to format time and date it’s best to understand a little of how Excel stores time and dates. By default on Windows based PCs, dates are stored in Excel sheets as the number of days since 0:00 1st Jan 1900 plus 1. Today (12th July 2010), is therefore stored as 40371. Time is stored as a fraction of a day, so 12:00 midday is stored as 0.5 and midday today would be 40371.5.
Times greater than 24 hours
By default, Excel displays times as hh:mm by taking the fractional part of the number stored. This works fine up to 23h59m59s but as soon as you go over 24 hours, then this method loses the fact that we’ve gone over a day and starts at 0:00 again, not very useful if you’re trying to create a timesheet.
To force Excel to take the digits to the left of the decimal point into consideration and display the real number of hours simply add square brackets to the ‘hh’ part of the format i.e. [hh]:mm
Negative Time
If you’ve ever created a calculation that results in a negative time (perfectly reasonable if you have a sheet which records say time owing) you will have seen the dreaded ########. Excel has two date systems, the older being the 1904 system used on Excel for Mac (Excel first appeared on the Mac before Windows even existed) and the 1900 system described above, which is used for increased compatibility with Lotus 1-2-3. While the 1904 system supports negative dates, the 1900 system does not.
To work with negative time values therefore requires switching to the 1904 system. On Excel 2000 this is found under Tools->Options on the Calculation Tab. Excel 2007 has it under Office Button->Excel Options-> Advanced.
Note that if you have already started your spreadsheet, all of your dates will now be out by 4 years and 1 day, so it is best to switch to the 1904 system before developing the sheet if you expect to require negative date display.