# convert to Julian day

#### jyokom

##### Board Regular
This should be easy to do but I am running into a few snags.

I am trying to convert the following:

A2 = 02-OCT-06 A3 = 6275

whereas A2 is the normal date and A3 is the converted Julian day. What is the simplest formula to enter into A3 to achieve this?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Code:
``=(YEAR(A2)-2000)*1000+A2-"1/1/2006"+1``

According to the help file under "Insert Julian dates:"
Use the TEXT and DATEVALUE functions to do this task.

[With the date in A2]
=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000") Date in "Julian" format, with a two-digit year (07174)

=TEXT(A2,"yyyy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000") Date in "Julian" format, with a four-digit year (2007174)

Note In the formula above, the year begins on January 1 (1/1). To convert the formulas to use a different starting date, edit the portion "1/1/" to the date you want.

This in Cell A2 should work.
Code:
``=RIGHT(YEAR(A1),1)&TEXT(A1-DATE(YEAR(A1),1,0),"000")``

=DAY(A2)+INT((153*MONTH(A2)-457)/5)+YEAR(A2)*365+INT(YEAR(A2)/4)-INT(YEAR(A2)/100)+INT(YEAR(A2)/400)+1721118.5

Looking at Von Pookie's solution, it made me realise that I've put a fixed year "2006" in my formula. That will need to be automated ... like this for example:
Code:
``=(YEAR(A2)-2000)*1000+A2-DATEVALUE("1/1/"&TEXT(YEAR(A2),"0000"))+1``

Looked at Glenn's solution, made me realise that my result was not a number but Text. So altered my formula too...
Code:
``=(RIGHT(YEAR(A1),1)&TEXT(A1-DATE(YEAR(A1),1,0),"000"))+1``

Okay, so I can't quite figure out what definition of Julian Day the rest of you (including the OP) are using

Perhaps there are other definitions? I have been referencing Peter Baum for many of my Date Calculations, and he defines Julian Days this way:

DEFINITION: The Julian Day Number, Julian Day, or JD of a particular instant of time is the number of days and fractions of a day since 12 hours Universal Time (Greenwich mean noon) on January 1 of the year -4712, where the year is given in the Julian proleptic calendar. The idea of using this reference date was originally proposed by Joseph Scalizer in 1582 to count years but it was modified by 19th century astronomers to count days. One could have equivalently defined the reference time to be noon of November 24, -4713 if were understood that Gregorian calendar rules were applied. Julian days are Julian Day Numbers and are not to be confused with Julian dates.

What am I missing?

Replies
3
Views
478
Replies
4
Views
536
Replies
1
Views
142
Replies
2
Views
190
Replies
3
Views
624

1,206,921
Messages
6,075,584
Members
446,147
Latest member
homedecortips

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back