First-timer question--custom date from a string of numbers?

[fre1102]

Hi, just found this forums searching for help online. I think I will be a regular here.

This may be a scripting question, but I'd like to change a column of dates formatted like this: "11130723", where character 1 is the century, 2 and 3 are the year, four and five are the month, and six and seven are the day (such that the date in the example is 7/23/2013--century "1" is the 21st century) into a more-commonly accepted, easier-to-read format.

...I cannot figure out how, short of opening four new columns, doing a mid in two columns to get the year and month, a right to get the date, then concatenating those three with slashes, then copy/paste-ing over that to get a date. I can do it pretty quick once I get the first row set up, but if I have seven or eight columns to format, well...I'd like a quicker way.

Anyone have any ideas?

Thanks in advance.

[FDibbins]

Hi and welcome to the forum

Maybe this?
=DATE(MID(A1,3,2),MID(A1,LEN(A1)-3,2),RIGHT(A1,2))

[TMS]

I think you have an extra "1" in your example. But, maybe:

Please Login or Register  to view this content.

Regards, TMS

[cffndncr]

Assuming your date is in A1:

=DATE((MID(A1,1,1))&MID(A1,2,2),MID(A1,4,2),MID(A1,6,2))

I also think you had an extra 1 in the example number you gave? That one had 8 digits, not 7.