Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default to convert a julian date back to regular date

How does one convert a julian date back to regular date?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default to convert a julian date back to regular date

)

"Lynn Hanna" wrote:

How does one convert a julian date back to regular date?



hi there
see http://www.cpearson.com/excel/jdates.htm (handy link to bookmark)

Converting A Standard Date To A Julian Date

The formula below will convert a standard Excel date in A1 to a Julian Date.

=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")

This formula takes the 2 right-most characters of the YEAR of the date in
A1, and then appends the number of days between the date in A1 and the 0th
day of that year. The TEXT function formats the day-of-year number as three
digits, with leading zeros if necessary.

Converting A Julian Date To A Standard Date

The formula below will convert a Julian date to a standard Excel date.

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))

If the year digits of the Julian date are less than 30 (i.e., 00 to 29), the
date is assumed to be a 2000 century year. If the year digits of the Julian
date are greater than or equal to 30 (i.e., 30 to 99), the date is assumed to
be a 1900 century year. This formula works by taking advantage of the fact
that the DATE function can handle days beyond the "normal" days in a month.
For example, DATE correctly computes 100-Jan-1999 to be 10-April-1999.

These Julian dates must have the leading zero or zeros for years between
2000 and 2009. For example the 123rd day of 2000 must be entered as 00123.
Format the cell as TEXT before entering the data, or enter an apostrophe
before the Julian date -- e.g., '00123. This will prevent Excel from
treating the Julian date as a number and suppressing the leading zeros.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to convert julian date to regular calendar date Ron Excel Worksheet Functions 5 May 5th 05 11:05 PM
how do I make a word typed in a cell go to a specific cell in anot Lmatarazzo Excel Discussion (Misc queries) 3 April 21st 05 04:29 AM
How do I convert a number formated as a date to text in Excel? BrotherNov Excel Discussion (Misc queries) 5 March 2nd 05 04:51 PM
julian date Jasonjetdoc Excel Discussion (Misc queries) 4 January 16th 05 05:39 PM
Roll back to previous date Jay Excel Worksheet Functions 2 December 3rd 04 04:35 PM


All times are GMT +1. The time now is 01:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"