Days Left in the Year

Summary: Sometimes it is handy to know how many days are left in the current year. This tip provides a quick formula that indicates the number remaining. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Do you ever need to figure out how many days are left in the current year? Since Excel stores dates as serial numbers (making them easy to subtract), it is easy to put together a formula that will return the number of days left in the year:

=DATE(YEAR(C12),12,31)-C12

This formula assumes that the date you want to analyze is in cell C12. It uses the DATE function to calculate the serial number for the last day of the year (December 31), and then subtracts the serial number for the actual date. The result is the difference—the number of days—between the two dates.

This formula returns a value that is formatted as a date. You will need to use Format | Cells to format the cell to a regular numeric value instead of a date.

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

Step Up and Take Control! Subscribers to ExcelTips know just how valuable a resource it is. ExcelTips Premium provides twice the number of exceptional, easy-to-understand tips every week in an ad-free newsletter, as well as substantial discounts on ExcelTips archives and e-books.

Check out ExcelTips Premium today!