how to add text(date) to a cell

[Venugopal]

Hi,

I am trying to check a condition and if it is true then

Cells(2,4).Value="Jan07".
But for some reason, the value Jan07 becomes 7-Jan in that cell.

Can anybody tell me how to correct this problem.


If (Cells(2, 2).Value = 20070101) Then
Cells(2, 4).Value = "Jan07"
End If

 

[Dave Peterson]

Do you want the Value Jan07 in that cell or do you want a date in that cell and
format it so that it shows Jan07?

With activesheet.cells(2,2)
if .value = 20070101 then
.numberformat = "@" 'text
.value = "Jan07"
end if
end with

or maybe...

With activesheet.cells(2,2)
.value = dateserial(left(.value,4),month(mid(.value,5,2), day(mid(.value,7,2))
.numberformat = "mmmyy"
end with

=====
If you have a column of this kind of number and want to convert that whole
column to a real date:

Select the single column range
data|text to columns
Fixed width (but remove any lines that excel guessed)
Choose date (ymd or ydm???)
and finish up

Then format that range the way you want.