Excel.Tips.Net Welcome toExcel.Tips.Net

Helpful Links

Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment

Tips.Net Store

ExcelTips FAQ
ExcelTips Premium

Learn Access Now
Free Printable Forms

Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

Newest Tips

Recording a Macro

Adding a Little Animation to Your Life

Converting a Range of URLs to Hyperlinks

Making the Formula Bar Persistent

Engineering Calculations

Digital Signatures for Macros

Fixing the Decimal Point

 

Calculating the Day of the Year

Summary: Excel allows you to perform math with date values. That means it is easy (as shown in this tip) to figure out what day of the year it is and how many days are left in the year. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)

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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2931) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Make Home Buying Less Stressful! Why make home buying harder than it needs to be? Put your mind at ease—discover all the questions you need to ask to make the best buying decision.
 
Check out Buying a Home Checklist today!