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.

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.