Microsoft Office Tutorials and References

In Depth Information

**Calculating with date and time**

TROUBLESHOOTING

I can’t enter a number of hours greater than 9,999

Suppose you have a worksheet in which you keep a running total of lying time for

pilots, using the time formats in Excel. Whenever you try to enter a number of hours

greater than 9,999 (which isn’t uncommon), Excel treats the entry as text. What’s

wrong?

Nothing is wrong—that’s just a built-in limitation of Excel. Here are a couple of ways to

work with this limitation:

●
Use an elapsed-time format. In the Cells group on the Home tab, click Format,

click Format Cells, click the Number tab if necessary, select the Custom category,

and then select [h]:mm:ss in the Type list to apply the built-in, elapsed-time

format. If you don’t need to record seconds, you can delete :ss. Elapsed-time

formats can store and display an unlimited number of hours.

●
If you need to enter more than 9,999 hours at a time, you have to break it into

two smaller chunks and type it in two cells.

You should also know that when you type a time greater than 24 hours (even 24:01),

Excel adds a date in the formula bar. Unless the number of hours typed exceeds a year’s

worth, the date Excel adds is sometime in 1900; you just have to live with that. You

can select the year in the formula bar and type the correct year, but then the year is

displayed in the cell along with the time. Otherwise, the date doesn’t show in the cell

unless you format it accordingly.

Calculating with date and time

Because Excel records dates as serial date values, you can use dates in formulas and

functions as you would any other value. Suppose you want to find the date that falls exactly 200

days after July 4, 2014. If cell A1 contains the entry 7/4/14, you can type the following

formula to compute the date 200 days later:
=A1+200
, which results in
1/20/15
(or the serial

date value
42024
).

As another example, suppose you want to find the number of weeks between October 31,

2003, and May 13, 2013. Type the formula
=(("5/13/13")–("10/31/03"))/7
, which returns

497 weeks.

You can also use times in formulas and functions, but the results of time arithmetic are

not as easy to understand as the results of date arithmetic. For example, you can

determine how much time has elapsed between 8:22 AM and 10:45 PM by typing the formula

="22:45"–"8:22"
. The result is
.599306
, which can be formatted using a 24-hour time