Date formatting not working

[jokkebal]

I receive a file with a date in the format e.g. 20111025. So this example signifies October 25th, 2011. I need the ability to work with it as a true date (in furhter downstream formulas). This is what I did : assuming that 20111025 stands in A1, I would enter following formula in B1 : =CONCATENATE(RIGHT(RC[-1],2),""/"",MID(RC[-1],5,2),""/"",LEFT(RC[-1],4)). This results in 25/10/2011. I then copy this formula, paste it as a value and format the cell as date (short). BUT : it doesn't work : another formula downstream does not recognise this result as a date.

Strange : when I press F2 "edit" on the cell, it works. Problem is that I have many lines, so I have to press F2 - down - F2 - down - F2 - down etc etc.

How can I resolve this ?

 

[Special-K99]

Try

=DATEVALUE(CONCATENATE(RIGHT(RC[-1],2),""/"",MID(RC[-1],5,2),""/"",LEFT(RC[-1],4)))

or even

=CONCATENATE(RIGHT(RC[-1],2),""/"",MID(RC[-1],5,2),""/"",LEFT(RC[-1],4))+0
Note +0 at the end

 

[Jonmo1]

Try

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 

[MickG]

You could try this for a list of dates in colun "A"

[COLOR="Navy"]Sub[/COLOR] MG27Aug02
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Dn.Value = DateSerial(Mid(Dn, 1, 4), Mid(Dn, 5, 2), Mid(Dn, 7, 2))
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jokkebal]

Hi all,

Thanks for your prompt and numerous replies.
I tried

=CONCATENATE(RIGHT(RC[-1],2),""/"",MID(RC[-1],5,2),""/"",LEFT(RC[-1],4))+0

This works perfectly.

Rgds.