convert to Julian day

[jyokom]

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?

 

[GlennUK]

How about:

=(YEAR(A2)-2000)*1000+A2-"1/1/2006"+1

 

[Von Pookie]

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.

 

[Datsmart]

This in Cell A2 should work.

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

 

[hatman]

How about this:

=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

 

[GlennUK]

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:

=(YEAR(A2)-2000)*1000+A2-DATEVALUE("1/1/"&TEXT(YEAR(A2),"0000"))+1

 

[Datsmart]

Looked at Glenn's solution, made me realise that my result was not a number but Text. So altered my formula too...

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

 

[hatman]

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?

 

[Datsmart]

More information about Dates and Time than you ever wanted to read.
http://www.decimaltime.hynes.net/dates.html

 

[Von Pookie]

May also be of interest:
http://cpearson.com/excel/jdates.htm