Given the Date in this spreadsheet, a simple formula using the DATE Function will return the Day of Year. Go ahead and type a Date value in cell A2 to see how the formula works, the spreadsheet is embedded from my SkyDrive. (Date format is US, “m/d/yyyy”)
Click here if you can’t see the embedded worksheet.
The Day of Year Formula
The formula in cell B2 is:
The DATE Function takes the Year from the Date you type in cell A2, and uses the zero (0) day of month 1 (January), which is the last day of the previous month. (I explained this in an earlier post).
The formula takes the Date you enter, and subtracts December 31st of the previous year to come up with the Day of Year.
You can copy the formula by double clicking cell B2, use Ctrl+A to select all the contents, and Ctrl+C to copy to the clipboard.
[UPDATE] Problems with SkyDrive
Apparently there are some problems with using the embedded worksheet on this web page. I added a public link to the file on Windows SkyDrive, but that seems to have some issues, depending on whether you use a Mac or Windows computer.
If you have problems with the embedded worksheet on this webpage, or with the web version on SkyDrive let me know in the comments what problem(s) you’re having, what hardware (Windows computer, Mac computer, iOS device – iPad, or other device) and what web browser (Firefox, Chrome, Safari, Opera, IE) you’re using. Thanks so much.