CODEWITHSUNDEEP
excelmathexcel-formulacalculated-columnsvba
flwr_pwr
flwr_pwr

Reputation: 170

Converting Julian Date (5 or 4 character) to Gregorian and vice versa

I'm trying to create a VBA script or a formula/function that converts a Julian Date (5001 or 15001) to a regular date (01/01/2015). I was able to find the following formula that converts a 5 character Julian Date to Gregorian Date and it works fine. I'm no mathematician so I'm not sure how to write the formula for the 4 character JD and I have no idea where to even start for the reverse (Regular Date to JD). Any help on this matter would be greatly appreciated.

The formula for the 5 character JD is here:

Assuming that the Julian Date is in A1:


=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

Thanks in advance for any help.

Upvotes: 3

Views: 5082

Answers (3)

barry houdini
barry houdini

Reputation: 46371

Do you need to accommodate dates before 2000? If not then you can use this formula:

=DATE(IF(LEN(A1)=5,LEFT(A1,2)+100,LOOKUP(YEAR(NOW()),FLOOR(YEAR(NOW()),10)+LEFT(A1)-{10,0})),1,RIGHT(A1,3))

That uses LOOKUP function to lookup the current year against the possible options, e.g. currently if you have 6001 in A1 that looks up 2015 against an array like {2006,2016} and the lower value is chosen. If you do the same calculation next year 2016 will be chosen.

This formula will work in to the future, e.g. in 2020 it will interpret 9001 as 1st Jan 2019.

For reversing the calculation do you always want a 4 digit JD, what about 2010 or 2020?

Upvotes: 0

Comintern
Comintern

Reputation: 22195

You need to test the length of the target cell to determine whether or not it is 4 or 5 characters long (line break for clarity):

=DATE(IF(0+(VALUE(IF(LEN(A1)=5,LEFT(A1,2),LEFT(A1,1))))<30,2000,1900)
      +VALUE(IF(LEN(A1)=5,LEFT(A1,2),LEFT(A1,1))),1,RIGHT(A1,3))

The reverse is much easier - just build a number out of the date parts and cast the resulting string to a number to get rid of any leading zero:

=VALUE(RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000"))

EDIT: Per comments, the following method will use the present decade if the first digit of a 4 year Julian date is less than or equal to the last digit of the current year, otherwise use 200x:

=DATE(IF(0+(VALUE(IF(LEN(A1)=5,LEFT(A1,2),LEFT(A1,1))))<30,
      IF(LEN(A1)=5,2000,IF(LEFT(A1,1)<=RIGHT(YEAR(NOW()),1),2010, 2000)),1900)
      +VALUE(IF(LEN(A1)=5,LEFT(A1,2),LEFT(A1,1))),1,RIGHT(A1,3))

Almost forgot... If you need the formula to remain valid after 2019 you can replace the 2010 with 2000+(MID(YEAR(NOW()),3,1)*10).

Upvotes: 2

bman
bman

Reputation: 5235

You can use this formula from this page:

=DATE(IF(0+(LEFT(JULIAN_DATE_CELL,2))<30,2000,1900)+LEFT(JULIAN_DATE_CELL,2),1,RIGHT(JULIAN_DATE_CELL,3))

You need to replace JULIAN_DATE_CELL with the cell number that you stored the Julian date.

Upvotes: 0

Related Questions