How about:

=DATEVALUE(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))


"Robert" wrote:

I want enter the date as 190805 (or any date) ie without any slash into A1.
In A2 it shoulddisplay as 19-Aug-05. Without using VBA can the MOD be
applied. I experimented the following formula (from this ng) changing "TIME"
to "DATE" but just cannot get it.

=TIME(INT(M1/10000),INT(MOD(M1,10000)/100),MOD(M1,100))

Thank you
Robert