Converting to Date Format
 

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.

Converting to 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.

Converting to 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.

Converting to 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.

Converting to 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

Converting to Date Format
 

=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

Converting to Date Format
 

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