ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   test to Julian date (https://www.excelbanter.com/excel-discussion-misc-queries/149465-test-julian-date.html)

curtev
test to Julian date
 
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
test to Julian date
 
Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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



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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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




Dave Peterson
test to Julian date
 
Maybe:
=MID(A1,3,2)
&TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1),"000")





curtev wrote:

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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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




--

Dave Peterson

Ron Coderre
test to Julian date
 
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" wrote:

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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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




curtev
test to Julian date
 
Thank you Dave and Roger for your help.

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


"Dave Peterson" wrote:

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





curtev wrote:

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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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




--

Dave Peterson

curtev
test to Julian date
 
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.



"Ron Coderre" wrote:

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" wrote:

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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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




Dave Peterson
test to Julian date
 
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 wrote:

Thank you Dave and Roger for your help.

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


"Dave Peterson" wrote:

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





curtev wrote:

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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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




--

Dave Peterson


--

Dave Peterson

curtev
test to Julian date
 
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.


"curtev" wrote:

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.



"Ron Coderre" wrote:

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" wrote:

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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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




Dave Peterson
test to Julian date
 
I'd do some more testing with that theory.

curtev wrote:

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.

"curtev" wrote:

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.



"Ron Coderre" wrote:

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" wrote:

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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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




--

Dave Peterson

Ron Coderre
test to Julian date
 
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


"curtev" wrote:

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.


"curtev" wrote:

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.



"Ron Coderre" wrote:

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" wrote:

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

Thanks much

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"curtev" wrote in message
...
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





All times are GMT +1. The time now is 12:48 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com