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

[Jeff]

Is this doable???

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.

 

[Dan E]

Give this a try

Private Sub CommandButton1_Click()
Selection.ClearFormats
For Each c In Selection
CellDate = c.Value
If Month(CellDate) <= 9 Then
OutMonth = CStr("0" & Month(CellDate))
Else
OutMonth = CStr(Month(CellDate))
End If
If Day(CellDate) <= 9 Then
OutDay = CStr("0" & Day(CellDate))
Else
OutDay = CStr(Day(CellDate))
End If
OutVal = CLng(Year(CellDate) & OutMonth & OutDay)
c.Value = OutVal
Next

End Sub

It will change all selected cells (NOTE: Backup before you run this!!!)
I used a commandbutton to launch it but you can use whatever you like

Dan E

 

[Dave Peterson]

I would think that just formatting the cell as yyyymmdd might be sufficient--but
I don't know what you do next and all I know about the AS/400 is to ask for a
text file so I can use it in excel!

But this might do what you want:

Option Explicit
Sub testme01()
Dim myCell As Range
Dim wks As Worksheet
Dim myInRng As Range
Dim myRng As Range

Set wks = ActiveSheet

With wks
Set myInRng = .Range("h:H")
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(myInRng, _
myInRng.Cells.SpecialCells(xlCellTypeConstants, xlNumbers))
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No dates found!"
Exit Sub
End If

For Each myCell In myRng
If IsDate(myCell.Value) Then
myCell.Value = "'" & Format(myCell, "yyyymmdd")
End If
Next myCell

End Sub

 

[Ron Rosenfeld]

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 Excel formula to do this conversion is:

=TEXT(A1,"yyyymmdd")

That leaves it as a text string.
If it needs to be numeric, just precede with a double unary:

=--TEXT(A1,"yyyymmdd")

However, a formula cannot change the contents of another cell. If that is
necessary, then to do something similar in VBA:

=================
Option Explicit
Sub ConvertDate()
Dim c As Range
For Each c In Selection
If IsDate(c) Then
c.Value = Format(c.Value, "yyyymmdd")
c.NumberFormat = "@"
End If
Next c
End Sub
================

will convert it in place to the text string or:

================
Option Explicit

Sub ConvertDate()
Dim c As Range
For Each c In Selection
If IsDate(c) Then
c.Value = Format(c.Value, "yyyymmdd")
c.NumberFormat = "General"
End If
Next c
End Sub
==================

if you want it converted in place to a numeric value.
--ron

 

[Jack Slater]

Can you repost - I missed it!

 

[Dan E]

http://www.google.ca/groups?hl=en&lr=&ie=UTF-8&threadm=9ef301c34668$8d1f7c
e0%24a401280a%40phx.gbl&rnum=1&prev=/groups%3Fq%3Dm/d/yyyy%2Bh:mm%2Bdate%2Bc
onversion%2Bto%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D9ef301c34668%25248d1f
7ce0%2524a401280a%2540phx.gbl%26rnum%3D1

or try the shorter version

http://makeashorterlink.com/?S35B22835

Dan E

 

[Jack Slater]

thanks! is there any formatting options at all to allow this in an existing
spreadsheet?

 

[Dave Peterson]

http://groups.google.com/[email protected]