I inherited a spreadsheet in which the dates were input as 18.02.06. How can
I best convert this string into a proper date format.

If they are always in that format, with two digits for day, month and
year, then you can use this, assuming the value is in A1:

=DATE("20"&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

Format the cell as a date. This assumes all years will be in this
century - if you have some dates with 99, 98, 97 etc this will need a
bit of modification. Copy the formula down as required.

Hope this helps.

Pete

On Nov 21, 12:50 am, KFred wrote:
I inherited a spreadsheet in which the dates were input as 18.02.06. How can
I best convert this string into a proper date format.

Here's the modification I mentioned:

=DATE(IF(RIGHT(A1,2)*1<30,"20","19")&RIGHT(A1,2),M ID(A1,4,2),LEFT(A1,2))

This assumes that years from 00 to 29 are in this century, and years
from 30 to 99 are in last century - change the 30 in the formula if
you want to make it a different break point.

Hope this helps.

Pete

On Nov 21, 12:50 am, KFred wrote:
I inherited a spreadsheet in which the dates were input as 18.02.06. How can
I best convert this string into a proper date format.

Thanks Pete,

Just curious what the "20"& signifies in the formula.

Fred

"Pete_UK" wrote:

If they are always in that format, with two digits for day, month and
year, then you can use this, assuming the value is in A1:

=DATE("20"&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

Format the cell as a date. This assumes all years will be in this
century - if you have some dates with 99, 98, 97 etc this will need a
bit of modification. Copy the formula down as required.

Hope this helps.

Pete

On Nov 21, 12:50 am, KFred wrote:
I inherited a spreadsheet in which the dates were input as 18.02.06. How can
I best convert this string into a proper date format.

=Right(A1,2)
will return "18"
So
"20"&"18"
will be
"2018"

Eleven years from now (about).

KFred wrote:

Thanks Pete,

Just curious what the "20"& signifies in the formula.

Fred

"Pete_UK" wrote:

If they are always in that format, with two digits for day, month and
year, then you can use this, assuming the value is in A1:

=DATE("20"&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

Format the cell as a date. This assumes all years will be in this
century - if you have some dates with 99, 98, 97 etc this will need a
bit of modification. Copy the formula down as required.

Hope this helps.

Pete

On Nov 21, 12:50 am, KFred wrote:
I inherited a spreadsheet in which the dates were input as 18.02.06. How can
I best convert this string into a proper date format.



--

Dave Peterson

=Right(A1,2), for the OP's example, would return 06, not 18, but the
principle was right, Dave!
--
David Biddulph

"Dave Peterson" wrote in message
...
=Right(A1,2)
will return "18"
So
"20"&"18"
will be
"2018"

Eleven years from now (about).

KFred wrote:

Thanks Pete,

Just curious what the "20"& signifies in the formula.

Fred

"Pete_UK" wrote:

If they are always in that format, with two digits for day, month and
year, then you can use this, assuming the value is in A1:

=DATE("20"&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

Format the cell as a date. This assumes all years will be in this
century - if you have some dates with 99, 98, 97 etc this will need a
bit of modification. Copy the formula down as required.

Hope this helps.

Pete

On Nov 21, 12:50 am, KFred wrote:
I inherited a spreadsheet in which the dates were input as 18.02.06.
How can
I best convert this string into a proper date format.



--

Dave Peterson

Right, left. I still get them mixed up!

Thanks for the correction, David.

David Biddulph wrote:

=Right(A1,2), for the OP's example, would return 06, not 18, but the
principle was right, Dave!
--
David Biddulph

"Dave Peterson" wrote in message
...
=Right(A1,2)
will return "18"
So
"20"&"18"
will be
"2018"

Eleven years from now (about).

KFred wrote:

Thanks Pete,

Just curious what the "20"& signifies in the formula.

Fred

"Pete_UK" wrote:

If they are always in that format, with two digits for day, month and
year, then you can use this, assuming the value is in A1:

=DATE("20"&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

Format the cell as a date. This assumes all years will be in this
century - if you have some dates with 99, 98, 97 etc this will need a
bit of modification. Copy the formula down as required.

Hope this helps.

Pete

On Nov 21, 12:50 am, KFred wrote:
I inherited a spreadsheet in which the dates were input as 18.02.06.
How can
I best convert this string into a proper date format.



--

Dave Peterson

--

Dave Peterson