Date Conversion

[dotsofcolor]

I have a series of dates that I need to convert. What is the best formula or way to convert the following. I have included some examples below.

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.

[JBeaucaire]

What dates do those strings convert to?

[dotsofcolor]

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.

Please Login or Register  to view this content.

Any suggestions on another method to work with this?

[fzz]

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

[protonLeah]

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

[holin_168]

Please check my attached file for solution. You need formula==DATE(20&MID(D2,2,2),1+IFERROR(MATCH(VALUE(RIGHT(D2,3)),D5:D16,1),0),VALUE(RIGHT(D2,3))-IFERROR(VLOOKUP(MATCH(VALUE(RIGHT(D2,3)),D5:D16,1),B5:D16,3,FALSE),0))



You may also interest in:
Extract report from a date range in Ms Excel

Combine duplicate Data in Ms Excel

[fzz]

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.

[dotsofcolor]

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.