m/d/yyyy h:mm date conversion to yyyymmdd ???

[Jeff]

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. Is this doable?

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.

 

[Don Guillett]

Can you just format at
=TEXT(Q3,"yyyymmdd")
or
for each c in selection
c.value =format(c,"yyyymmdd")
next

 

[Ron Rosenfeld]

See answers in misc NG

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. Is this doable?

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.

--ron