Calculate The Day Of The Year And Days Remaining

Category: Formulas | [Item URL]

If you've ever had to figure out which of the year's 365 days a particular date falls on, or how many days remain in the year, you've probably found that Excel lacks functions to perform the calculation. But you can create formulas to do the job.

The formula below returns the day of the year for a date in cell A1:

`=A1-DATE(YEAR(A1),1,0)`

Note: Excel automatically formats the cell as a date, so change the number format to another option (like General).

To calculate the number of days remaining in the year (assuming that the date is in cell A1), use the following formula:

`=DATE(YEAR(A1),12,31)-A1`

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers