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
Search JabSto ::




Custom Search