Excel can accept time values using most of the common conventions for designating time. For a user who is completely unfamiliar with the way Excel handles time values, see Working With Time Values in Microsoft Excel. When the intent is to display time durations, rather than clock times, a user will need to approach things somewhat differently.
How to Enter Time Durations in Excel
For a user who needs to enter time durations of less than 24 hours, everything is very simple. For example:
- Enter 1: to display 1:00 (one hour)
- Enter 0:01 to display 0:01 (one minute)
- Enter 2:30 to display 2:30 (two hours, thirty minutes)
- Enter 0:00:30 to display 0:00:30 (thirty seconds)
Notice that if a user enters data that includes seconds, Excel defaults to a time format that displays seconds. If the durations exceed 23 hours, Excel also defaults to the hh:mm:ss format whether or not the user has included minutes or seconds in the entry.
- Enter 23: to display 23:00
- Enter 24: to display 24:00:00
- Enter 200: to display 200:00:00
The maximum time duration that can be entered is 9999:59:59. This holds true for Excel 2003, Excel 2007, and Excel 2004 for the Mac.
Displaying Durations Before and After Using Negative Time
A user will quickly see that Excel is not happy with -1: –something that might seem very reasonable to a user!
To use negative times, a user must have the option set for the 1904 date system. For users not familiar with the two different date systems in Excel, see Working With Dates in Microsoft Excel. This is the Mac’s default option, so for Windows users only do this:
- Excel 2007 for Windows – Click Excel Options, Advanced, When calculating this worksheet
- Excel 2003 for Windows – Click Tools, Options, Calculation
- Click to change the option “1904 date system.”
Once the user is operating under the 1904 date system, negative times can be calculated, but not entered. To see this in action do the following:
- Enter 0: in cell A11 and name this cell as a range, TAKEOFF
- Enter 10: in cell B1, 9: in cell B2
- Highlight B1 and B2
- Use fill handle to populate cells B3 through B10 with decreasing time duration values
- In cell A1 enter the formula =TAKEOFF – B1 and copy this formula in A2 through A10.
- There are many variations on how this could be used, but this illustrates the idea. To recap:
Negative times cannot be entered
Negative times can be calculated when the 1904 date system is turned on.
A More Complex Example Using Negative Dates
When a time value is entered, Excel makes a “best guess” for the format the user intended. That is, Excel assigns a format. If the time entered is less that 24 hours, the time format assigned is hh:mm; Excel displays hours and minutes only. If the time is greater than 24 hours, the hh:mm:ss format is assigned; Excel displays seconds even if they are not intended by the user.
The little trick with these formats is that the hh:mm format will not display values greater than 23:59. So, once a cell is formatted with the hh:mm format, if a user then decides to change the contents of that cell, and enters 25:00 for instance, the user will see 1:00 rather than the expected 25:00. The solution is to reformat the cell with the hh:mm:ss format. To visualize how this could cause frustration, exasperation, and nail biting from an unsuspecting user do the following:
- Start with a new blank workbook and set date option to 1904 (for Windows)
- Enter 0: in cell A6 (displays 0:00)
- Name A6 as a range (BLASTOFF is a good example)
- In cell B1, enter 50: and in cell B2 enter 40: then continue down column B with 30:, 20:, and 10:
- In cell A1 enter =BLASTOFF-B1 and copy this formula cells A2 and A3.
- In cell A4 enter =BLASTOFF-B4 and copy this formula to A5
Pay attention here! Rows 1-3 are formatted differently than Rows 4 and 5. Now, to see how things could go wrong:
Enter 25: in cell B4
This demonstrates that the hh:mm format does not just display differently–only hours and minutes—it’s on a 24-hour clock! The number 25 rolls around to 1 a.m.! This can be easily remedied by changing the cell format, but a user encountering this situation for the first time deserves to know in advance!
Armed with this knowledge any Excel user can schedule and monitor events much easier— even rocket launches!
