Help using Format to change a date?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed - 26 Jan 2006 13:26 GMT

I have a table with a column of dates. All the dates are in the form of
"20050625" (yyyymmdd). I tried a few variations of Format to change this
to "25 Jun 2005" (dd mmm yyyy), but just couldn't get it. This is the code
I left off with - any suggestions?

Ed

Sub XX_ChgDates()

Dim rng As Range
Dim cll As Cell
Dim str1 As String
Dim str2 As Date
Dim str3 As Date

' Cells to be changed are selected
Set rng = Selection.Range

For Each cll In rng.Cells
str1 = cll.Range.Text
str1 = Left(str1, (Len(str1) - 2))
str2 = str1
str3 = Format(str2, "dd mmm yyyy")
cll.Range.Text = str3
Next cll

End Sub

Reply to this Message

Kevin B - 26 Jan 2006 14:02 GMT

You can parse the date string and reassemble it into the date format of your
liking. The following 2 formulas will produce mm/dd/yyyy and dd Month yyyy
respectively:
-------------------------------------------------------------------------------------------------
Function MyDate(DateString As String) As String

Dim strYear As String
Dim strDay As String
Dim strMonth As String

strYear = Left$(DateString, 4)
strMonth = Mid$(DateString, 5, 2)
strDay = Right$(DateString, 2)

MyDate = strMonth & "/" & strDay & "/" & strYear

End Function
-------------------------------------------------------------------------------------------------
Function MyDate2(DateString As String)

Dim strYear As String
Dim strDay As String
Dim strMonth As String

strYear = Left$(DateString, 4)
strMonth = Mid$(DateString, 5, 2)
strDay = Right$(DateString, 2)

Select Case strMonth
Case "01"
strMonth = "January"
Case "02"
strMonth = "February"
Case "03"
strMonth = "March"
Case "04"
strMonth = "April"
Case "05"
strMonth = "May"
Case "06"
strMonth = "June"
Case "07"
strMonth = "July"
Case "08"
strMonth = "August"
Case "09"
strMonth = "September"
Case "10"
strMonth = "October"
Case "11"
strMonth = "November"
Case "12"
strMonth = "December"
End Select

MyDate2 = strDay & " " & strMonth & " " & strYear

End Function
-------------------------------------------------------------------------------------------------

Signature

Kevin Backmann

> I have a table with a column of dates. All the dates are in the form of
> "20050625" (yyyymmdd). I tried a few variations of Format to change this
[quoted text clipped - 23 lines]
>
> End Sub

Reply to this Message

Ed - 26 Jan 2006 14:17 GMT

Thanks, Kevin. That kind of thing was going to be my next attempt! I had
thought, though, that the Format function would automatically change things
around. Maybe I just really misunderstood how to use it.

I appreciate the boost.
Ed

> You can parse the date string and reassemble it into the date format of your
> liking. The following 2 formulas will produce mm/dd/yyyy and dd Month yyyy
[quoted text clipped - 83 lines]
> >
> > End Sub

Reply to this Message

Kevin B - 26 Jan 2006 14:32 GMT

Format works if it can interpret the input as a real date, like "Jan 1, 2003"
or "1-1-05". A string of 8 digits is not read as a valid date.

--
Kevin Backmann

> Thanks, Kevin. That kind of thing was going to be my next attempt! I had
> thought, though, that the Format function would automatically change things
[quoted text clipped - 97 lines]
> > >
> > > End Sub

Reply to this Message

Ed - 26 Jan 2006 15:14 GMT

Just like most of my problems - I don't understand what I'm doing! 8>)
Thanks much, Kevin. I appreciate the help.
Ed

> Format works if it can interpret the input as a real date, like "Jan 1, 2003"
> or "1-1-05". A string of 8 digits is not read as a valid date.
[quoted text clipped - 106 lines]
> > > >
> > > > End Sub

Reply to this Message

Ed - 26 Jan 2006 15:23 GMT

Kevin, I'm having trouble using your MyDate2 function. Not because it
doesn't work, but because I don't understand enough of how to use a
function! I'm trying to get its output, but can't quite figure it out. I
know it needs DateString to feed into your function, but I can't get MyDate2
into my sub. Can you drop-kick me in the right direction, please?

Ed

Sub XX_ChgDates()

Dim rng As Range
Dim cll As Cell
Dim DateString As String

Set rng = Selection.Range

For Each cll In rng.Cells
DateString = cll.Range.Text
DateString = Left(DateString, (Len(DateString) - 2))

cll.Range.Text = MyDate2

Next cll

End Sub

Reply to this Message

Ed - 26 Jan 2006 15:32 GMT

Never mind! I finally figured out that the "Argument Not Optional" error
message meant I wasn't passing the argument into the function. So I changed
cll.Range.Text = MyDate2
to
cll.Range.Text = MyDate2(DateString)
and it works great now! Thanks again.

Ed

> Kevin, I'm having trouble using your MyDate2 function. Not because it
> doesn't work, but because I don't understand enough of how to use a
[quoted text clipped - 119 lines]
> > >
> > > End Sub

Reply to this Message

Kevin B - 26 Jan 2006 16:05 GMT

I should have provided a bit more of an explanation, but I'm glad I was able
to be of assistance.

Signature

Kevin Backmann

> Never mind! I finally figured out that the "Argument Not Optional" error
> message meant I wasn't passing the argument into the function. So I changed
[quoted text clipped - 130 lines]
> > > >
> > > > End Sub

Reply to this Message

Rate this thread: