VB Format command fails

[kirkm]

Hi,

Anyone know what's wrong here?

Cell(1,1) contains "00/11/1975" as text.

Dim v as Date
v = Format(.Cells(1, 1), "mmm yyyy")

If v is dimmed as a Variant the format is unchanged
and in the above example returns 'type mismatch'.

Have spend a long time trying all sorts of variations
without success.

Thanks - Kirk

 

[OssieMac]

Hi Kirk,

Format function reutrns a string value not a date value and hense cannot be
assigned to a date variable.

00/11/75 is not a valid date and you can't even use DateValue to return a
date from it.

You can use Val but it will return zero.

What are you trying to achieve? There may be another way.

 

[kirkm]

On Thu, 30 Apr 2009 00:13:01 -0700, OssieMac

Hi Ossie

Format function reutrns a string value not a date value and hense cannot be
assigned to a date variable.

Aha... OK

00/11/75 is not a valid date and you can't even use DateValue to return a
date from it.

You can use Val but it will return zero.

What are you trying to achieve? There may be another way.

Change '00/11/75' into "Nov 1975".

I normally go with some pretty basic stuff but was sure VB would
know "11" was November and 1975 was... 1975... if I could
figure out how! I have used Format with "mm ddd yyyy" but
this was to output a string (as you say).

Thanks - Kirk

 

[kirkm]

On Thu, 30 Apr 2009 08:17:40 +0100, Simon Lloyd

Hi Simon

Have you qualified .Cells(....etc?,
Yes

why not supply a bit more of your
code,

Fair enough, but it was just that line - or method failing. Ossie has
explained Format won't return a date.

which version of Excel are you using?, you say the value is
enetered as text have you added a ' for a text entry or is the cell
formatted as text?

2002 and the Cell is formatted as text. I found anything else caused
no end of trouble with auto-converting to ... something quite
different e.g. date to a number. Very frustrating!!

Cheers - Kirk

 

[Harald Staff]

Hi Kirk

The others explained about types. Problem also is that it won't convert a
month-like text without a day to a valid date, therefore the type mismatch.
So let's avoid that one:

Sub test()
Dim S As String
Dim D As Date
'S = Cells(1, 1).Value 'or
S = "00/11/1975"
S = Replace(S, "00/", "01/")
D = DateValue(S)
MsgBox Format(D, "dddd dd.mm.yy") & vbNewLine & _
Format(D, "mmm yyyy")
End Sub

HTH. Best wishes Harald

in message news:[email protected]...

 

[OssieMac]

Hi Kirk,

I still don't know exactly what you want to do with the mmm yyyy format. Do
you simply want it to display like that on the worksheet? I have a similar
thing that I use with AutoFilter so that it will display the month and year
in a column against another column that contains actual dates so that I can
select the month in Autofilter without having to use a custom filter.
However, the date behind the cell is actually 1 Nov 1975 for all of the
November dates.

If various dates are in column A then Custom Format column B to "mmm yyyy"
Now if date is in cell A2 then in B2 insert:-
=DATEVALUE(TEXT(A2,"mmm yyyy"))

In VBA code it would be as follows:-

Sub test()
With Sheets("Sheet1")
.Columns(2).NumberFormat = "mmm yyyy"
.Cells(2, 2) = DateValue(Format(.Cells(2, 1), "mmm yyyy"))
End With
End Sub

Hope this helps.

 

[kirkm]

On Thu, 30 Apr 2009 13:05:04 -0700, OssieMac

Hi Ossie,

Thanks for that example. I see how that would work.

I still don't know exactly what you want to do with the mmm yyyy format. Do
you simply want it to display like that on the worksheet?

Yes - here's what I ended up using, although now I see Haralds example
this is laughable!

--
Sub UpDateDate()
Dim j, y, c, m, D
D = "JanFebMarAprMayJunJulAugSepOctNovDec"
With Worksheets("Sheet1")
For j = 2002 To 4000
c = .Cells(j, "B") '00/00/1965'
If c > "" Then
y = Right(c, 4)
m = Val(Mid(c, 4, 2))
Select Case m
Case 1 To 12
m = (m * 3) - 2
.Cells(j, "B") = Mid(D, m, 3) & " " & y
Case 0
.Cells(j, "B") = y
Case Else
Stop
End Select
End If
Next j
End With
End Sub
--

If various dates are in column A then Custom Format column B to "mmm yyyy"
Now if date is in cell A2 then in B2 insert:-
=DATEVALUE(TEXT(A2,"mmm yyyy"))

In VBA code it would be as follows:-

Sub test()
With Sheets("Sheet1")
.Columns(2).NumberFormat = "mmm yyyy"
.Cells(2, 2) = DateValue(Format(.Cells(2, 1), "mmm yyyy"))
End With
End Sub

One for the book. I'd not seen DateValue before.

Thanks - Kirk

 

[kirkm]

Hi Kirk

The others explained about types. Problem also is that it won't convert a
month-like text without a day to a valid date, therefore the type mismatch.
So let's avoid that one:

Sub test()
Dim S As String
Dim D As Date
'S = Cells(1, 1).Value 'or
S = "00/11/1975"
S = Replace(S, "00/", "01/")
D = DateValue(S)
MsgBox Format(D, "dddd dd.mm.yy") & vbNewLine & _
Format(D, "mmm yyyy")
End Sub

Hi Harold,

Gotcha! That's exactly where I was heading....

Plus I nearly fell over when seeing the Replace command.
I've made lengthy custom routines that do that is a far less
efficient way. (But I found it's not in Access 97 so don't
feel completely stupid!). DateValue was new to me, too.

So much to remember!

Thanks - Kirk

 

[Harald Staff]

Plus I nearly fell over when seeing the Replace command.
I've made lengthy custom routines that do that is a far less
efficient way. (But I found it's not in Access 97 so don't
feel completely stupid!). DateValue was new to me, too.

You're welcome Kirk.
Replace came with VB6 / VBA 2000. Very useful new text functions was
Replace, Split, Join and InstrRev. I use the first two all the time, like:

Sub test()
Dim Sentnc As String
Dim Words() As String
Dim i As Long
Sentnc = InputBox("Say something:")
Words = Split(Sentnc, " ")
For i = LBound(Words) To UBound(Words)
MsgBox Words(i), , "Word " & i & ":"
Next
End Sub

Best wishes Harald