# Excel - Day of year to date

Bren.ie 10:59 12 Feb 2004
Locked

Is it possible to convert the day of year to a date.(Assuming all number are for 2004)

Example 001 to 01/01/2004

or 366 to 31/12/2004

recap 12:17 12 Feb 2004

That is until say the likes of VoG sees this posting.

Peverelli 13:54 12 Feb 2004

You could always put the numbers 1 to 366 in cells A1 to A366. Then in cell B1 enter the formula " =A1+37986 ". Then click on B1 and, holding the mouse button, scroll down to B366. Then format column B to Date and this should give you 1/1/2004 for B1, 2/1/2004 for B2 etc.

Pete1 14:03 12 Feb 2004

A neater solution would be using the DATEDIF function . Enter 31/12/2003 in say A1. If the date in 2004 was in A2 then the formula would be =DATEDIF(A1,A2,"D")

Megatyte 14:05 12 Feb 2004

Format cells as custom and in the Type: box enter dd/mm/yy. This will return a date in the year 1900, so you must add 37986 to each number that you enter. You can do this by entering 37986 in a blank cell (eg A1). In your target cell(which has been formated as above) enter =B1 + A1 (where B1 is your source cell.

AH

Megatyte 14:07 12 Feb 2004

Late again. Oh well!!

Megatyte 14:14 12 Feb 2004

Just looked at your solution again and it would appear that you've got it the wrong way. DateDif looks at dates and returns a number. What is required is to look at a number and return a date.

AH

Pete1 14:20 12 Feb 2004

yes i should have read the original posting a little more closely

Megatyte 14:26 12 Feb 2004

Correct my earlier posting to..

...enter =B1 + \$A\$1 (where B1 is your source cell.)

AH

Bren.ie 17:01 12 Feb 2004

Cesar 11:30 13 Feb 2004