Excel - Day of year to date

  Bren.ie 10:59 12 Feb 04
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 04

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 04

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 04

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 04

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 04

Late again. Oh well!!

  Megatyte 14:14 12 Feb 04

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 04

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

  Megatyte 14:26 12 Feb 04

Correct my earlier posting to..

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

AH

  Bren.ie 17:01 12 Feb 04

Thanks everyone for your help

  Cesar 11:30 13 Feb 04

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…