Excel - Day of year to date

  Bren.ie 10:59 12 Feb 2004

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

click here and download the zip version it may be of some help?
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.


  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.


  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.)


  Bren.ie 17:01 12 Feb 2004

Thanks everyone for your help

  Cesar 11:30 13 Feb 2004

click here.
Scroll down and in the left column you will find "Excel Tips com" download that and you will find plenty of tips on Excel.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy A8 review: Hands-on

Illustrator Juan Esteban Rodriguez on creating highly detailed official film posters for Star Wars…

iMac Pro review

Meilleurs drones (2018)