Help to convert a date to julian date fromat yyyyddd in excel [Solved/Closed]

seekermay 28 Posts Wednesday January 27, 2010Registration date May 27, 2013 Last seen - May 9, 2013 at 01:51 AM - Latest reply:  Calixte DORVIL
- Sep 30, 2013 at 06:44 PM
hi,

i have thousands of dates in format dd-mm-yy that needs to be converted to julian date format yyyyddd. dates that needs to be converted are from period range of 01-may-13 to 31-dec-19. I am using MS excel for this purpose.
pls help me out to convert these dates to julian date with format yyyydd.

would realy appriciate your help in this regard..
Thanks...
May
See more 

6 replies

Best answer
sgmpatnaik 54 Posts Tuesday April 2, 2013Registration date November 27, 2013 Last seen - May 9, 2013 at 09:05 AM
3
Thank you
Hi

Then Try this Formula

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

Hope it will solve your problem, other wise please inform

we will wait for your response

Thanks


Patnaik

Thank you, sgmpatnaik 3

Something to say? Add comment

CCM has helped 1712 users this month

seekermay 28 Posts Wednesday January 27, 2010Registration date May 27, 2013 Last seen - May 27, 2013 at 03:47 AM
great it works..

Thank You
seekermay 28 Posts Wednesday January 27, 2010Registration date May 27, 2013 Last seen - May 9, 2013 at 07:21 AM
2
Thank you
thank you for reply.

i think I have mis interpretted above. now trying to explain it as simple as to convert the date as per below example.

From date format: 09-May-13
To be converted to julian date as: 2013129

would appriciate your help once again and accept my apoogy for inconvenience.

regards...may
sgmpatnaik 54 Posts Tuesday April 2, 2013Registration date November 27, 2013 Last seen - May 9, 2013 at 06:33 AM
0
Thank you
@seekermay

HI

Please Try the below formula

say your date stored in the Column A2:A then type the Formula in Column B

=TEXT($A2,"YYYY")&" - "&TEXT($A2,"DD")

with the above formula you will get the date as

01-05-2013

with my formula

2013 - 01

Hope it will solve your problem, other wise please inform us

Thanks

Patnaik
Julian =CONCATENATE(YEAR(A1),IF(A1-DATE(YEAR(A1),1,1)+1<10,CONCATENATE("00",A1-DATE(YEAR(A1),1,1)+1),IF(A1-DATE(YEAR(A1),1,1)+1<100,CONCATENATE(0,A1-DATE(YEAR(A1),1,1)+1),A1-DATE(YEAR(A1),1,1)+1)))
Kevin@Radstock 42 Posts Thursday January 31, 2013Registration date April 26, 2014 Last seen - May 9, 2013 at 12:31 PM
0
Thank you
Here you go seekermay, some reading for you regarding Julian dates.


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