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, 2008. If cell A1 contains the entry 7/4/08, you can type the following formula to compute the date 200 days later, which is 1/20/09 (or 39,833): =A1+200.

As another example, suppose you want to find the number of weeks between October 31, 2003, and May 13, 2008. Type the formula =(("5/13/08")-("10/31/03"))/7, which returns approximately 236.6 weeks.

You can also use times in formulas and functions; however, 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 format (one that doesn't include AM/PM) to display 14:23. Excel displays the result relative to midnight.

Suppose you want to determine the time that is 2 hours, 23 minutes, and 17 seconds after 12:35:23 PM. The formula =("12:35:23 PM")+("2:23:17") returns the correct answer, .624074, which can be formatted as 14:58:40. In this formula, 2:23:17 represents not an absolute time (2:23:17 AM) but an interval of time (2 hours, 23 minutes, and 17 seconds). This format is perfectly acceptable to Excel.

TROUBLESHOOTING 

Excel displays my time as #####.

Usually, a cell full of number signs means the cell isn't wide enough to show its displayed contents. But Excel can't display negative numbers as dates or times. If the result of a date or time calculation is negative and you attempt to display this result in a date or time format, you will see a cell full of number signs, no matter how much you widen the cell. This typically happens when you subtract a later time of day from an earlier time of day. You can work around the problem by converting the result to elapsed hours. To do that, multiply the result by 24, and display it in a numeric format, not a date or time format.