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:


The reason that the first formula uses a date of 12/31/2012 is so the result will show the actual day number. Using these formulas, the result of 1/1/2013 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. Display the Home tab of the ribbon.
  2. Click the small icon at the lower-right corner of the Number group. Excel displays the Format Cells dialog box.
  3. Make sure the Number tab is selected.
  4. In the Category list, choose Number. (See Figure 1.)
  5. Figure 1. The Number tab of the Format Cells dialog box.

  6. Make sure the Decimal Places option is set to 0.
  7. Click on OK.