Converting to Date Format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Here's the modification I mentioned:

=DATE(IF(RIGHT(A1,2)*1<30,"20","19")&RIGHT(A1,2),MID(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
 
=Right(A1,2)
will return "18"
So
"20"&"18"
will be
"2018"

Eleven years from now (about).
 
=Right(A1,2), for the OP's example, would return 06, not 18, but the
principle was right, Dave!
 
Back
Top