m/d/yyyy h:mm date conversion to yyyymmdd ???
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. |
m/d/yyyy h:mm date conversion to yyyymmdd ???
See answers in misc NG
On Wed, 9 Jul 2003 15:18:48 -0700, "Jeff" wrote: 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 |
All times are GMT +1. The time now is 08:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com