MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Day of the Year


Posted by Bob C on December 01, 2001 8:19 AM

How do I get Excel to let me know what day of the year it is? For example December 1 is the 335th day of this year.

Posted by Mike Winters on December 01, 2001 8:24 AM

Try this formula (this was taken from another posting on this board, I do not claim it).
=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")
That will convert a standard date in cell A1 to a Julian date. There is more info at his website at
http://www.cpearson.com/excel/jdates.htm
Hope that helps.

Sincerely,

Mike Winters
United States Army

Posted by Bob C on December 01, 2001 8:47 AM

Forgive me, I should have indicated why I need this. I need to be able to track sales as a percentage of where we should be in relation to the yearly sales goal. For instance, June 30 was the 181st day of the year, so at that point sales should be 49.6 % of the yearly goal
(181/365=.49589).

Posted by Mike Winters on December 01, 2001 9:03 AM

Ok, put this formula instead:
=TEXT(A1-DATE(YEAR(A1),1,0),"000")
This returns just the day of the year, then you can use a formula in column C to do percentages
IE:
In cell A1, I have 6/30/2000
In cell B1, I have =TEXT(A1-DATE(YEAR(A1),1,0),"000")
In cell C1, I have =(B1/365)
Also, I formatted the entire C column to percentage, with 2 decimal places, so it comes out as 49.86%
You can then, of course, pretty it up, by placing headings at the top of each column, but this should be functional.

Sincerely,

Mike Winters
United States Army

Posted by IML on December 01, 2001 3:30 PM

If leap years matter in your calculation, I'd suggest using
=(A1-DATE(YEAR(A1),1,0))/(365+ISNUMBER(("2/29/"&YEAR(A1))+0))
where A1 is the date of interest. Or in two cells, B1 could be
A1-DATE(YEAR(A1),1,0)
and C1
=b1/(365+ISNUMBER(("2/29/"&YEAR(A1))+0))

Format the cells in all cases as "general".
good luck

Posted by BobC on December 04, 2001 1:13 PM

Thank you for all the assistance.