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

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

=DATE(RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

Mangesh



"Robert" wrote in message
...
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

Duke, thank you very much for the formula. BTW isn't there a MOD
function for this.

Mangesh, your formula actually has the date as 15-Aug-(19)05
instead of 15-Aug-(20)05.
Robert