Convert text to date

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi,

I have downloaded data from a database that stored dates as 1001201 for
1/12/2000 and 991201 for 1/12/1999.

I have used the following to convert this into a string that looks like a
date

=IF(LEFT(D2,1)="9",CONCATENATE(RIGHT(D2,2),"/",MID(D2,3,2),"/",LEFT(D2,2)),C
ONCATENATE(RIGHT(D2,2),"/",MID(D2,4,2),"/",MID(D2,2,2)))

However, what I would really like to do is make excel see the converted
string as a date rather than a piece of text that LOOKS like a date

What do I need to do to achieve this?

Thanks
 
Hi
try
=IF(LEFT(D2,1)=9,DATE("19"&LEFT(D2,2),MID(D2,3,2),RIGHT(D2,2)),DATE("20
"&MID(D2,2,2),MID(D2,4,2),RIGHT(D2,2))
and format the cell as date
 
Database's date format must be constant and I assume you down load th
date format is YYMMDD, but due to Excel's date format is Month, Day
Year, so ........


=VALUE(MID(D2,3,2) & "/" & RIGHT(D2,2) & "/" & LEFT(D2,2))


Hope it can solve your proble
 
Thanks - I just had to add " " around the 9 for the if and it works
perfectly

Thanks again
 
Another way:

=(REPLACE(RIGHT(A1,4),3,,"-")&"-"&LEFT(RIGHT(A1,6),2))*1

HTH
Jason
Atlanta, GA
 
Hi,

I have downloaded data from a database that stored dates as 1001201 for
1/12/2000 and 991201 for 1/12/1999.

I have used the following to convert this into a string that looks like a
date

=IF(LEFT(D2,1)="9",CONCATENATE(RIGHT(D2,2),"/",MID(D2,3,2),"/",LEFT(D2,2)),C
ONCATENATE(RIGHT(D2,2),"/",MID(D2,4,2),"/",MID(D2,2,2)))

However, what I would really like to do is make excel see the converted
string as a date rather than a piece of text that LOOKS like a date

What do I need to do to achieve this?

Thanks
Click to expand...

If the dates are YMD, then

=DATE(INT(A1/10^4)+1900,MOD(INT(A1/100),100),MOD(A1/100,1)*100)

If YDM, then reverse the last two arguments.


--ron
 
Back
Top