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.

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.

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.

.

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