[jeff]

I have a cell with a custom date format of m/d/yyyy h:mm,
I need to convert the date to yyyymmdd text. Does anyone
have a VB macro code snippet to do something like this?
I've tried text to columns but can't get it to work...any
help is appreciated.

Thanks.

[Bob Phillips[_5_]]

Jeff,

See response in misc group

--

HTH

Bob Phillips

"jeff" wrote in message
...
I have a cell with a custom date format of m/d/yyyy h:mm,
I need to convert the date to yyyymmdd text. Does anyone
have a VB macro code snippet to do something like this?
I've tried text to columns but can't get it to work...any
help is appreciated.

Thanks.

[Patrick Molloy[_3_]]

You aren't clear. Does the cell CONTAIN as date or is it
just the format that you want to change?
Try changing the format to General. If its a 'date' you
will see the cell value as a number like 36752. If you
still see a text date, then the cell is text.
You could convert to a date using the DateValue()
function.
Now all you need to do is formatthe cell yyyymmdd

if you need the text in a cell
=TEXT(DateValue(A1),"YYYYMMDD")
where A1 is your text date

HTh
Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
I have a cell with a custom date format of m/d/yyyy
h:mm,
I need to convert the date to yyyymmdd text. Does
anyone
have a VB macro code snippet to do something like this?
I've tried text to columns but can't get it to
work...any
help is appreciated.

Thanks.

.

[Dave Peterson[_3_]]

See a couple of replies in .misc.

Jeff Wysocki wrote:

I'll try to be more specific.

I want to use the functionality of excel/vb to change the actual current
value of a cell, for example, 7/9/2003 9:41 to "20030709". The current
format of the cell is custom m/d/yyyy h:mm. I need the actual value of
the cell to be 20030709, not a formula or the excel date/time serial
number. I am uploading this file to a Physical File on an AS/400. It's
easy enough for me to manipulate the field once I get the data to the
AS/400, I just would like to try to format the data correctly in the
spreadsheet before uploading...just looking to see what I can/cannot do
in excel/vb.

I've used a for/next loop in a macro to change date cells to text using
text to columns, now I want to do the same but change the date format to
YYYYMMDD also. The end result of the cell must be actual data, not a
formula.

Here is what I've used in the past:

Dim CurCell As Object
Columns("H:H").Select
For Each CurCell In Selection
If CurCell.Value < "" Then CurCell.TextToColumns Destination:=CurCell,
DataType:=xlFixedWidth, _
OtherChar:="/", FieldInfo:=Array(0, 2)
Next

Once again, any help is appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson