test to Julian date

C

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
 
C

curtev

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

Thanks much
 
D

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")
 
R

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
 
C

curtev

Thank you Dave and Roger for your help.

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

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.
 
D

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.
 
C

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.
 
D

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.
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top