Julian date to standard date

[doqa]

I need a Julian date turned into a standard date. However, the only catch is the Julian date is attached to a company code. E.g., I need to be able to take the 300 from 300XXXX and turn it into 10/27/2010.

Then I need to be able to turn 10/27/2010 to 10/27/2011 (our product has an expiration date of a year from when it is produced). So the end result is I need to be able to take the first four numbers from a number, turn that Julian date into a standard date, and then add a year to that standard date.

Thanks in advance for your help.

 

[barry houdini]

You're right about the leap year, the way I was doing it won't work. Assuming you always have 8 digits (including leading zero if applicable, like 02461843) then this formula will get the correct date and add a year, taking leap years into account

=EDATE(DATE(2010+LEFT(A2),1,MID(A2,2,3)),12)

Note: if you are using Excel 2003 or earlier you need Analysis ToolPak enabled to use EDATE

If you might have missing leading zeroes then try

=IF(LEN(A2)=8,EDATE(DATE(2010+LEFT(A2),1,MID(A2,2,3)),12),DATE(2011,1,LEFT(A2,LEN(A2)-4)))