Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How can I convert Day of the Year to a mm/dd format in Excel?

Date of the Year: e.g. 1= Jan 1, 177 = June 27.

For example, how do I convert "177" to 6/27?

share|improve this question
    
177 is not June 27; it is June 25 in leap year (31+29+31+30+31+25=177) and June 26 otherwise (31+28+31+30+31+26=177). –  Scott Aug 1 '13 at 0:36

2 Answers 2

Add 41274 to it =A1+41274 and then

Right click - Format Cells - custom and type in mmm dd or mmmm dd if you want the month name.

This will work for year 2013 because Jan 01, 2013 is 41,274 days after Jan 01, 1900

share|improve this answer
1  
You'll need to add the offset from 1/1/1900 to the year required (41274 days for 2013) as it's different for leap years –  hdhondt Aug 1 '13 at 0:22

For numbers greater than 59 (31+28), the answer is different for leap year and non-leap year.  To get the answers for the current year, put

=DATE(YEAR(TODAY()), 1, A1)

in a helper column and format as you wish.  To use some other year, use

=DATE(yyyy, 1, A1)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.