Would like an explanation of a formula:

[Guest]

=("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

 

[Dave Peterson]

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.

 

[David Biddulph]

Well that formula converts 2006031 to 31 Jan 3906, rather than to 31 Jan
2006, so I wouldn't feel inclined to use it as an answer to that question.
If that works like a charm, I would want a different charm.

Did you start with
=("1/1/"&(IF(LEFT(A2,2)*1<20,2000,1900)+LEFT(A2,2)))+MOD(A2,1000)-1 which
was intended to solve a slightly different problem? I believe that this
formula was intended to deal with an input of 06031, and using the
assumption that dates are between 1920 and 2019.
You can't just change the LEFT(...,2) to LEFT(...,4).

=("1/1/"&LEFT(A12,4))+MOD(A12,1000)-1 or
=("1/1/"&LEFT(A12,4))+RIGHT(A12,3)-1 would seem OK at first glance for the
question you are tring to solve.

 

[Guest]

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]

=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.

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