=DATE(LEFT(dq10,4),1,RIGHT(dq10,3))
And format the result as a date.

=date(year,month,day)
so this formula essentially does:
=date(2006,1,031)

So the first argument is the year, the second is the month, and the third is the
day.

Leo wrote:

Thanks loads, sure makes the conversion simple, not to mention something I
can easily remember, but what does the "1" do in the middle of the formula.--
Leo

"Dave Peterson" wrote:

First, it didn't work for me. With 2006031 in DQ10, I got 01/31/3096 as the
result.

It's trying to build a date string 1/1/someyear + SomeNumberOfDays

This portion:
(IF(LEFT(DQ10,4)*1<20,2000,1900)
is trying to determine the century of the date (19xx or 20xx). But you could
just look at the first 4 characters to find that.

Then it adds left(DQ10,4) (which is 2006) to the year.

the mod(dq10,1000) portion returns 31. The formula subtracts 1 from this, since
they started with "1/1/".

I don't think I'd use that formula. I'd use something like:

=DATE(LEFT(dq10,4),1,RIGHT(dq10,3))
And format the result as a date.

=date(year,month,day)
so this formula essentially does:
=date(2006,1,031)

If you had 2006211, the formula would be like:
=date(2006,1,211)
The 211 day of January in 2006.

Excel is pretty smart when it comes to dates. It can determine that this is
really July 30, 2006.




Leo wrote:

=("1/1/"&(IF(LEFT(DQ10,4)*1<20,2000,1900)+LEFT(DQ10,4)))+ MOD(DQ10,1000)-1

The other day I needed a formula that would change a Julian date to a
calendar date, e.g., 2006031 to 01/31/06. I googled the question and found
the answer, for which I am grateful, in a formula provided by Joseph Rubins
Excel Tips. It worked like a charm and I was wondering if someone would take
the time to explain exactly how it works. Thanks

--
Leo

--

Dave Peterson


--

Dave Peterson