Formula to determine how many days have passed using the year and Julian date

Question

I have a form that I built in Excel that uses the MID formula to deconstruct an eight-digit numeric code (for example 15317087) into three parts and generate the corresponding output for each part. The first part 15is the year. The second part is Julian day 317, and the last part 087represents the county number from which the code was sent.

Here's what it looks like:

Please note that it is 15not used. This is because on another page I have a number representing the total number of days elapsed between 2015 and ... well, regardless of the start date "0" in Excel. As expected, this number for 2015 is 42002. To create a Gregorian date, I use:

INT(SUM(MID(A2,3,3), 'Date Conversion'!A$1))  

which simply adds this number to the Julian date.

Next year's problem, I will have to change the value Date Conversion'!A$1from 42002 to 42002 + 365 manually, because not every year has the same number of days. Is there any mathematical way to account for this, or should I update this field manually?

Answer 1

y3k, "15" :

=DATE(2000+VALUE(LEFT(A2,2)),1,1)

1 , 2000 3000 . , .

, , Excel, 1 2015 ( ) 42005 [ 1 1900 ]. .