Constructing Date and Time Formulas

You already have some experience entering dates and times in a worksheet and then changing how they appear by assigning different date and time number formats. Remember that all you need to do when you enter a date or time number in a worksheet is to emulate one of these date or time number formats when making the entry.

The only thing the least little bit baffling about date and time entries in a worksheet is the actual way that Excel stores the date and time numbers into a cell when you make an entry following one of the standard number formats. Behind the scenes (that is, the date or time number formats) lies a serial number. In the case of dates, this serial number represents the number of days that have elapsed between the date you enter and the beginning of the twentieth century (making January 1, 1900 serial date number 1). In the case of time, the serial number is a fraction representing the number of hours, minutes, and seconds that have elapsed since midnight (which is serial number 0.00000000, so that twelve noon is 0.50000000).

To build a simple date formula that calculates the number of days that have elapsed between two dates entered in the worksheet, subtract the cell containing the starting date from the one containing the ending date. Excel then displays the calculated result in a date format, which you then have to reformat with another number format (such as Number with no decimal points) to see the number of days that this "date" actually represents.

To build a simple time formula that calculates the fractional part of the day that has elapsed between two times entered in the worksheet, you subtract the cell containing the starting time from the one containing the ending time. Excel then displays the calculated result as a fraction that you can then convert into hours by multiplying by 24.

When entering times in a spreadsheet that you intend to use in formulas to compute elapsed times, be sure to enter all times after twelve noon either using a twenty-four clock (as in 13:05 for 1:05 PM) or with the PM signifier after the time number. Otherwise, Excel interprets all the times you enter as occurring in the AM.