date format cell, to text format

[sunilpatel]

i have column "C" formated as a date format ("dd-mmm-yy").
I did this by selecting column c and then clicking format>cells>date..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

 

[Stefi]

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

„sunilpatel†ezt írta:

 

[Rick Rothstein]

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.