Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert date format yyyymmdd to mm/dd/yyyy | Excel Worksheet Functions | |||
how do I change the date format from yyyymmdd to mm/dd/yyyy | Excel Worksheet Functions | |||
Date Conversion dd.mm.yyyy to dd/mm/yyyy | Excel Worksheet Functions | |||
basically want to go from mm/dd/yyyy to yyyymmdd | Excel Worksheet Functions | |||
date conversion from m/d/yyyy to yyyymmdd | Excel Programming |