» Converting a Julian Date to a Calendar Date

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

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:

=("1/1/"&(IF(LEFT(A2,LEN(A2)-3)*1<20,2000,1900)+LEFT(A2,LEN(A2)-3)))+MOD(A2,1000)-1

Here's another:

=("1/1/"&(IF(INT(A2/1000)<20,2000,1900)+INT(A2/1000)))+MOD(A2,1000)-1

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

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]

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

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

Big thanks
tp