test to Julian date

[curtev]

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

 

[Roger Govier]

Hi

Try
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1)

 

[curtev]

That almost did the trick. I need the two digit year in front as in: 3/20/1996
to 96080

Thanks much

 

[Dave Peterson]

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

 

[Ron Coderre]

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

 

[curtev]

Thank you Dave and Roger for your help.

For 19960320, using your formula I am getting 96079, but it should be 96080.

 

[curtev]

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.

 

[Dave Peterson]

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.

 

[curtev]

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.

 

[Dave Peterson]

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.

 

[Ron Coderre]

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