Calculating the Day of the Year

You've probably seen it on calendars before--some include an indication that "Today is the 123 day of the year with 242 remaining." You can easily calculate the day number of a year, as well as how many are remaining. For instance, let's assume you have a date in cell D27. You could use the following formulas to calculate, respectively, what day of the year it is and how many are still left:

=D27-"12/31/2005"
="12/31/2006"-D27

The reason that the first formula uses a date of 12/31/2005 is so the result will show the actual day number. Using these formulas, the result of 1/1/2006 in cell D27 would result in 1, meaning it is the first day of the year. (This is as it should be.)

Of course, once you enter the formulas, you need to format the cells as regular numbers. (Excel will, by default, try to format the cells as dates.) With the two cells selected, follow these steps:

1. Choose Cells from the Format menu. Excel displays the Format Cells dialog box.
2. Make sure the Number tab is selected. (Click here to see a related figure.)
3. In the Category list, choose Number.
4. Make sure the Decimal Places option is set to 0.
5. Click on OK.