Chaneg value to Date format within same field A1

Hi All,
I have in field A1, value of 20131021 in format general.

I want to convert this to 21/10/2013 within the same field. (Field A1)

The only way I know is to do it in another cell, then references it.
e.g. A2 =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

But is it possible to use any methods to change the date format within A1 itself?

Regards.
Nironto

Thanks Alkey, Maybe I got your formula wrong. it is no difference to to what I have. ^_^
A2 =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
See attachment

Regards,
Nironto

I need them within the same field. (Field A1). Maybe likely to be a underlying VBA program. Since A1 is also the field you keyed into.
The fields I can convert them to date otherwise if they are in another field (say A2), but due to requirements that A1 you enter the date in 20131021, it will populate 21/10/2013 via an underlying / hidden vba or formula.

Regards,
Nironto

In VBA you can capture a change event and keep monitoring the new value entered in a cell. Once you have the 8 digits entered, then you can format the value into string and assign to the same cell. Something like:

Please Login or Register  to view this content.

- Jaideep

Thanks Jaideep.This works!

Regards,
Nironto