Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel - Converting Julian Date to a Calendar Date 1
- Thread starter aug4570
- Start date
-
1
- #2
Follow this link:
- Thread starter
- #4
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),"000"
)
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 Tejanorey
"Do not attack the First Marine Division. Leave the yellowlegs alone. Strike the American Army."
- 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
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),"000"
)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 Tejanorey
"Do not attack the First Marine Division. Leave the yellowlegs alone. Strike the American Army."
- 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
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.
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
IS-IT--Management
Try:
=MID(C8,5,2)&"/"&RIGHT(C8,2)&"/"&LEFT(C8,4)
where C8 is the cell of your long date
=MID(C8,5,2)&"/"&RIGHT(C8,2)&"/"&LEFT(C8,4)
where C8 is the cell of your long date
- Status
Similar threads
- Locked
- Question
- Locked
- Question