i have column "C" formated as a date format ("dd-mmm-yy").
I did this by selecting column c and then clicking formatcellsdate..and
selecting "14-Mar-98" in right hand section.

So when i enter 40018 into cell "C1" then cell reads "24-Jul-09"

i need to put some statement into the adjacent cell ("D1") which if
formatted as "text", so it reads the cell content not the value.

i.e range("C1").value="400018", I need to have "D1" read "24-Jul-09". I
tried to copy and paste special but there is no "Text" option.

My problem is that there are around a 20,000 rows popolated. How can
iachieve this please?

Sunil

Enter this formula in D1:
=TEXT(C1,"dd-mm-yy")
Regards,
Stefi

€žsunilpatel€� ezt Ã*rta:

i have column "C" formated as a date format ("dd-mmm-yy").
I did this by selecting column c and then clicking formatcellsdate..and
selecting "14-Mar-98" in right hand section.

So when i enter 40018 into cell "C1" then cell reads "24-Jul-09"

i need to put some statement into the adjacent cell ("D1") which if
formatted as "text", so it reads the cell content not the value.

i.e range("C1").value="400018", I need to have "D1" read "24-Jul-09". I
tried to copy and paste special but there is no "Text" option.

My problem is that there are around a 20,000 rows popolated. How can
iachieve this please?

Sunil

Right click the tab at the bottom of the worksheet that is to have this
functionality, select View Code from the pop up menu that appears and
copy/paste the following into the code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And IsDate(Target.Value) Then
If CDate(Target.Value) < DateSerial(1900, 1, 1) Then Exit Sub
Application.EnableEvents = False
With Target.Offset(, 1)
.NumberFormat = "@"
.Value = Format(Target.Value, "dd-mmm-yy")
End With
Application.EnableEvents = True
End If
End Sub

Now, go back to your worksheet... whenever you enter a valid Excel date in
Column C, the text entry you want will automatically be made into Column D.

--
Rick (MVP - Excel)


"sunilpatel" wrote in message
...
i have column "C" formated as a date format ("dd-mmm-yy").
I did this by selecting column c and then clicking formatcellsdate..and
selecting "14-Mar-98" in right hand section.

So when i enter 40018 into cell "C1" then cell reads "24-Jul-09"

i need to put some statement into the adjacent cell ("D1") which if
formatted as "text", so it reads the cell content not the value.

i.e range("C1").value="400018", I need to have "D1" read "24-Jul-09". I
tried to copy and paste special but there is no "Text" option.

My problem is that there are around a 20,000 rows popolated. How can
iachieve this please?

Sunil