Date Conversion

The strings should convert to the following I believe if I am converting them correctly.

110356 12/22/2010
110351 12/17/2010
110364 12/30/2010
111003 1/3/2011

I have this formula to work with which seems to be working but I can't help wondering if this is the best way to work with these types of Julian Dates especially when there are as many as I have to work with, and it's a lot.

=DATE(YEAR("01/01/"&TEXT(1900+INT(E22/1000),0)),MONTH("01/01/"&TEXT(1900+INT(E22/1000),0)),DAY("01/01/"&TEXT(1900+INT(E22/1000),0)))+MOD(E22,1000)-1

Any suggestions on another method to work with this?

Originally Posted by dotsofcolor

...
The 110 at the beginning represents 2010 and the 111 at the beginning represents 2011.

110356
110351
110364
111003

Also would the above actually be considered Julian dates? I have seen them with 2 digits and 4 digits representing the year but not normally 3.

You'd need to specify the format, not anyone else here. If the last 3 digits are Julian dates, then it appears the next 2 digits before that would be the year within the specified century, and I'd guess the 1 meant 2000 thru 2099, so 0 would mean 1900 thru 1999, so presumably 2-digit years would be between 1900 and 1999 since the leading 0 for 099002 for 2 Jan 1999 may have been dropped. Further, I'd guess 4-digit years are as-is, so 2011364 would be 30 Dec 2011.

If so, try

=DATE(IF(LEN(A1)<7,1899,0)+INT(A1/1000),12,31)+MOD(A1,1000)

Originally Posted by protonLeah

This one "seems" to work:
=DATE(MID(A1,2,2)+2000,1,0)+RIGHT(A1,3)

It should unless there were any dates in the 1900s. Then it may fail.

I tried them all and they worked great. All of them helped me with the direction I needed to go and I learned a few things along the way. Always great help in this forum and it's really appreciated.