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

Jeff

unread,

Jul 9, 2003, 6:22:15 PM7/9/03

to

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

unread,

Jul 9, 2003, 6:55:02 PM7/9/03

to

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

"Jeff" <jwys...@lodan.com> wrote in message
news:9ef301c34668$8d1f7ce0$a401...@phx.gbl...

Peter Atherton

unread,

Jul 9, 2003, 7:38:39 PM7/9/03

to

Jeff
Select the data, choose Format, Cell, Custom and type the
format yyyymmdd h

To use this in a VB procedure use something like

With Selection
NumberFormat = "yyyymmdd h"
End With

Regards
Peter

>.
>

Dave Peterson

unread,

Jul 9, 2003, 10:07:32 PM7/9/03

to

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

--

Dave Peterson
ec3...@msn.com

Ron Rosenfeld

unread,

Jul 9, 2003, 10:51:05 PM7/9/03

to

On Wed, 9 Jul 2003 15:22:15 -0700, "Jeff" <jwys...@lodan.com> 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 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

Jeff

unread,

Jul 10, 2003, 10:45:14 AM7/10/03

to

Thanks to everyone for the great VBA examples, this is
exactly what I was looking for...it works perfectly!

Jeff

Jack Slater

unread,

Jul 10, 2003, 10:49:12 AM7/10/03

to

Can you repost - I missed it!

"Jeff" <Jwys...@lodan.com> wrote in message
news:a81801c346f1$dfa48670$a401...@phx.gbl...

Dan E

unread,

Jul 10, 2003, 11:14:36 AM7/10/03

to

http://www.google.ca/groups?hl=en&lr=&ie=UTF-8&threadm=9ef301c34668%248d1f7c
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" <jack....@cnh.com> wrote in message
news:uaqjyJvR...@TK2MSFTNGP10.phx.gbl...

Jack Slater

unread,

Jul 10, 2003, 11:37:25 AM7/10/03

to

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

"Dan E" <nospam....@nospam.mailcity.com> wrote in message
news:OSSx4XvR...@TK2MSFTNGP11.phx.gbl...

Dave Peterson

unread,

Jul 10, 2003, 2:23:21 PM7/10/03

to

http://groups.google.com/groups?threadm=3F0CCA64.16776C3A%40msn.com

"Jack Slater" <jack....@cnh.com> wrote in message news:<uaqjyJvR...@TK2MSFTNGP10.phx.gbl>...