Excel - Converting Julian Date to a Calendar Date 1

[aug4570]

Anyone know how? Thanks.

 

[carrr]

Follow this link:

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

 

[carrr]

...or use this:
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

 

[aug4570]

Thanks carrr. That worked!

 

[Tejanorey]

here is another way:

CONVERTING JULIAN DATES
Here's a little piece of post-Y2K calendar trivia for you. Since the 1950s, astronomers and geophysicists have used Julian dates to track time in continuous, absolute terms, without bothersome seasons, leap years, or man-made conventions. Julian dates associate a date with the number of days elapsed since January 1 of the same year. (For example, Jan. 2, 2000, has the value of 2.)

Excel doesn't have a built-in option to convert dates to Julian time, but you can use this shortcut to convert today's date to the Julian format.

Just type this equation into any cell in your worksheet:

=VALUE(RIGHT(YEAR(TODAY()),2)&TEXT(TODAY()-DATE(YEAR(TODAY())-1,12,31),&quot;000&quot)

Now, you'll need to format this value to display five digits.
1.Right-click the cell that contains the value and
select Format Cells.
2.On the Number tab, choose Custom from the Category list
box.
3.In the Type text box, enter 00000.
4.Click OK.

ref from

http://www.techrepublic.com

Tejanorey

&quot;Do not attack the First Marine Division. Leave the yellowlegs alone. Strike the American Army.&quot;
- Orders given to Communist troops in the Korean War; shortly afterward, the Marines were ordered to not wear their khaki leggings to keep the enemy from immediately fleeing

 

[007ab]

I have the following data in Excel and would like to use the date function to return the exact year as shown.

18960526
18960527
18960528
18960529
18960601

When I use the date function and change the cell format to display dd/mm/yyyy Excel returns the year as 3096 (ading 1900 to 1896), how can I get excel to return the original year format.

 

[bluedragon2]

Try:

=MID(C8,5,2)&&quot;/&quot;&RIGHT(C8,2)&&quot;/&quot;&LEFT(C8,4)

where C8 is the cell of your long date

 

[tav1035]

bluedragon2,
Hate to tag on, but I used bluedragon's to convert date from
ie. 20030320 to 03/20/2003
tav