test to Julian date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be
converted to Julian date. Can this be done without first converting to date
format? Thanks
 
That almost did the trick. I need the two digit year in front as in: 3/20/1996
to 96080

Thanks much
 
Maybe:
=MID(A1,3,2)
&TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1),"000")
 
With
A1: (a number representing YYYYMMDD)

Try this:
=MID(A1,3,2)&MID(1000+TEXT(A1,"0000\/00\/00")-("12/31/"&(LEFT(A1,4)-1)),2,3)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Thank you Dave and Roger for your help.

For 19960320, using your formula I am getting 96079, but it should be 96080.
 
Ron, that works great. Thank you all for your help and patience.

I am confused, however. Using Excel's formula of
=RIGHT(YEAR(G1),2)&TEXT(G1-DATE(YEAR(G1),1,0),"000") on date format of
3/20/1996, it returns 96080

But using a converter on the Internet, and the first two formulas above, I
get 96079. Why is that? Which is correct?

Thank you very much for your help.
 
It would depend on whether you wanted to include that end day:

=MID(A1,3,2)
&TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,0),"000")

notice that this portion:
DATE(LEFT(A1,4),1,0)
pretty much matches
DATE(YEAR(G1),1,0)
that you posted in the other branch.
 
Aha. I have just realized that this is a leap year, therefore the different
answers. Two formulas are needed. One for a regular year and another for a
leap year.

So I guess I need an IF statement to determine which formula to use. Can
you help me with that?

Again thank you for your help.
 
I'd do some more testing with that theory.
Aha. I have just realized that this is a leap year, therefore the different
answers. Two formulas are needed. One for a regular year and another for a
leap year.

So I guess I need an IF statement to determine which formula to use. Can
you help me with that?

Again thank you for your help.
Click to expand...
 
I've seen a fair amount of confusion about what a julian date is. I followed
Chip Pearson's coverage them at his website:

http://www.cpearson.com/excel/jdates.htm

Per his first formula, the julian date for 20-MAR-1996 is 96080. In a leap
year, 20-MAR-1996 is the 80th day, otherwise it's the 79th. The formula I
posted accommodates both.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Back
Top