CODEWITHSUNDEEP
exceldateexcel-formula
Musixauce3000
Musixauce3000

Reputation: 559

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

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

Here's how it looks:

Picture

Notice how 15 isn't used. This is because on another page I have a number representing the total number of days passed between 2015 and... well whatever the initial "0 date" is in Excel. As it would happen, that number for 2015 is 42002. To produce the Gregorian date, I use: 

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

which just adds that number to the Julian date.

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

Upvotes: 4

Views: 365

Answers (1)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3833

If you're willing to run into a y3k problem, you could convert the '15' into a year as follows:

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

This give you Jan 1 of whatever year is in the left 2 numbers, resetting to the year 2000 during calendar year 3000. Presumably changing the formula at that time will not be burdensome.

So, the Julian calendar used by Excel shows Jan 1 2015 (per my above formula) as 42005 [number of days since Jan 1 1900]. This can then be inserted into your conversion to Gregorian.

Upvotes: 1

Related Questions