Excel computes days between dates

Here’s a nifty use for Excel: figuring out how long it is until your next vacation. Excel can easily perform arithmetic on dates because it stores them as a plain old number.

The next time you are moping in your cubicle wondering when you can head off to the Bahamas, you can fire up Excel, look like you’re working and calculate how long it is until you are free.

Here’s how you do it. Suppose your trip to the Bahamas begins on April 12, 2007, and today is Nov. 16, 2006.

Excel stores dates as a regular serial number. In Excel’s worldview, time began on Jan. 1, 1900. That was day one, January 2, 1900 was day two, and so forth.

To begin your calculations, type a date into cell A1. Excel understands slashes, so type 11/16/2006. If you click in the cell and choose Format|Cells, you’ll see Excel knows it is a date (the calculations won’t work if the number is not formatted as a date).

Now in cell A2, type in 4/12/2007. To find out how many days it is, you just do simple subtraction. In cell A3, type =A2-A1 to subtract Nov. 16 from April 12.

The result, however, is formatted as a date, which throws you off. Now, with your cursor in Cell A3, choose Format|Cells and change the type to General.

You can now tell that the result is 147 days.

If you want to really get fancy, you can use one of Excel’s date formulas.

For example, the NETWORKDAYS formula calculates how many work days there are between two dates. In cell A4, type =NETWORKDAYS(A1,A2). You find that you have only 106 days of cubicle time left.

If you get an #NAME? error, it means you don’t have the Analysis ToolPak add-in installed. Choose Tools|Add Ins and see if it is in the list.

If so, place a check mark next to it. If you don’t see any add-ins listed, you may need to install them from your Office CD.