Date to Text conversion

[Michael Koerner]

I have a sheet with a two columns containing dates. Sometimes it is the full
date as an example 25-Jul-1936, or Jul-1936 or 1936 I would like to be able
to convert that to text and maintain the same face value. Is that possible
with some sort of macro?

 

[Ronald R. Dodge, Jr.]

Look at the NumberFormat Property on the Range Object to see how the data is
formatted, which then you can use the VBA.Format(<Value>,<Format>) to
convert the value to text in the same manner.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Steven B]

I have a sheet with a two columns containing dates. Sometimes it is the full
date as an example  25-Jul-1936, or Jul-1936 or 1936 I would like to beable
to convert that to text and maintain the same face value. Is that possible
with some sort of macro?

Sub DateText ()
Dim DtStr as String
Dim Rng as Range
Dim R as Range

Set Rng = Range("A:B" NOTE: REPLACE WITH RANGE CONTAINING DATES)

For each R in Rng
DtStr = R.value
r.NumberFormat = "@"
r.value = DtStr
DtStr = ""
Next R

End Sub

Untested code, please backup your work before applying.


Steven

 

[Dave Peterson]

Select the range to fix first:

Dim myRng as range
dim myCell as range

set myrng = selection
myrng.numberformat = "@"
for each mycell in myrng.cells
mycell.value = mycell.text
next mycell

..text is what you see in the cell (after formatting). If the column is too
narrow, you could see ###'s and this will keep those ###'s--so be a little
careful.

 

[Michael Koerner]

All this did was change 25-Jul-1936 to 25/07/1936 thanks.

--

Regards
Michael Koerner

I have a sheet with a two columns containing dates. Sometimes it is the
full
date as an example 25-Jul-1936, or Jul-1936 or 1936 I would like to be
able
to convert that to text and maintain the same face value. Is that possible
with some sort of macro?

Sub DateText ()
Dim DtStr as String
Dim Rng as Range
Dim R as Range

Set Rng = Range("A:B" NOTE: REPLACE WITH RANGE CONTAINING DATES)

For each R in Rng
DtStr = R.value
r.NumberFormat = "@"
r.value = DtStr
DtStr = ""
Next R

End Sub

Untested code, please backup your work before applying.


Steven

 

[Michael Koerner]

Tried your solution. All I got in place of the dates was a bunch of numbers.
Thanks

--

Regards
Michael Koerner


Select the range to fix first:

Dim myRng as range
dim myCell as range

set myrng = selection
myrng.numberformat = "@"
for each mycell in myrng.cells
mycell.value = mycell.text
next mycell

..text is what you see in the cell (after formatting). If the column is too
narrow, you could see ###'s and this will keep those ###'s--so be a little
careful.

 

[Dave Peterson]

Try:

Dim myRng As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
myStr = .Text
.NumberFormat = "@"
.Value = myStr
End With
Next myCell

 

[Michael Koerner]

Dave;

That worked. Now how do I replace the - with a space. when I do a search and
replace, it changes the whole thing back into a dd-mmm-yy which is different
from the original dd-mmm-yyyy sequence.

--

Regards
Michael Koerner


Try:

Dim myRng As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
myStr = .Text
.NumberFormat = "@"
.Value = myStr
End With
Next myCell

 

[Dave Peterson]

change this line:
myStr = .Text
to:
myStr = replace(.Text,"-"," ")

But replace was added in xl2k.

If you're using xl97 (or have to support it):
myStr = application.substitute(.Text,"-"," ")

 

[Michael Koerner]

My bad, should have mentioned that I'm using 2007. will give you change a
shot. Thanks very much

--

Regards
Michael Koerner


change this line:
myStr = .Text
to:
myStr = replace(.Text,"-"," ")

But replace was added in xl2k.

If you're using xl97 (or have to support it):
myStr = application.substitute(.Text,"-"," ")

 

[Michael Koerner]

Worked like a charm. thank you very much

--

Regards
Michael Koerner


My bad, should have mentioned that I'm using 2007. will give you change a
shot. Thanks very much

--

Regards
Michael Koerner


change this line:
myStr = .Text
to:
myStr = replace(.Text,"-"," ")

But replace was added in xl2k.

If you're using xl97 (or have to support it):
myStr = application.substitute(.Text,"-"," ")