Listed in column A are Julian dates in "YYJJJ" format.
"YY" represents a year between 1920 and 2020, and "JJJ" represents the serial number of the day within the year.
We want to convert each date in column A to its corresponding calendar date.
Solution:
Use the LEFT, IF, and MOD functions as shown in the following formula and format the results as dates:
=("1/1/"&(IF(LEFT(A2,2)*1<20,2000,1900)+LEFT(A2,2)))+MOD(A2,1000)-1
Inserting calendar in VBA form to have date inputs
dwijraj wrote on December 01, 2005 13:15 EST
Hi,
I want to include the calendar in my VBA form to take date inputs from the calendar.
I do not know exactly how that can be done.
Thanks
Dwij
Revised formula to handle Julian dates with leading zeros
Paul Laba wrote on January 10, 2006 09:49 EST
This conversion formula works great, but will not work correctly if the Julian date cell is numeric and the date's year field contains a leading zero (e.g., "03365"). In that case Excel will strip the leading zero, and the conversion formula will produce an erroneous result.
To illustrate, enter the Julian date value 03365 in cell A2 (formatted as General, Number, Currency, etc.). The value displayed in cell A2 will be 3365 (no leading zero). The converted date in cell B2 will be 12/31/1933 instead of 12/31/2003.
One way to deal with this leading zeros problem is to ensure the cell containing the Julian date is formatted as Text; Excel will treat any entries in the cell "as is" and not strip any leading zeros.
You can also work around this leading zero problem by entering the Julian date with a leading apostrophe ('03365); Excel will then treat your entry as text, even though the cell itself might not be formatted as Text.
Best would be to use a conversion formula that correctly handles Julian date values that contain only a single digit year. Here one formula that does that:
The second formula is preferred, as it treats the Julian date value as a number and makes no assumption about the "length" of the value.
The second formula will even convert Julian dates with *no* year field (the Julian date value '365' is converted to 12/31/2000).
Paul
Doesn't work well with 2000 + dates
Dcdrj2 wrote on January 12, 2006 16:09 EST
With dates > 2000 example 05339 the date redutured is 12/05/53 when it should be 12/05/05:rolleyes:
[QUOTE=ExcelTip]Problem:
Listed in column A are Julian dates in "YYJJJ" format.
"YY" represents a year between 1920 and 2020, and "JJJ" represents the serial number of the day within the year.
We want to convert each date in column A to its corresponding calendar date.
Solution:
Use the LEFT, IF, and MOD functions as shown in the following formula and format the results as dates:
=("1/1/"&(IF(LEFT(A2,2)*1[/QUOTE]
converting Julian date to calendar date
tpham wrote on June 07, 2006 17:20 EST
problem:
I have colunm A : 2005 145 23:25
I want to convert it to calenader date like:
5/25/2005 23:25
but i don't know how to do , please help
Big thanks
TP
Converting a Julian Date to a Calendar Date
tammy wrote on December 31, 1969 18:00 EST
problem
colunm A: 2005 145 22:25
I want to convert to calendar date such:
05/25/2005 22:25
I don't know how to convert it to corresponding calendar date. please help me
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Investing for Dummies, Third Edition
