Using TEXT(A1,"mmyy") in VBA?

[Drofidnas]

Hi

By using

=IF(TEXT($I18,"mmyy")=TEXT(L$2,"mmyy"),1,0)

I can perform a check of the two cells and even though one is formatted as Oct-14 an the other is 23/10/14

I will get a 1 - ie, they are in the same month.

How can I do this programmatically with VBA?

I have

If Cells(18,9) = Cells (2,12) then
Cells (4,2) = 1 
End If

but I need some formatting around those statements.

Thanks for any help

Chris

 

[JoeMo]

If Format(Range("I18"),"mmyy") = Format(Range("L2"),"mmyy") Then
'Do something
End If

 

[bbott]

You could try:

If [COLOR=#ff0000][B]Month([/B][/COLOR]Cells(18,9)[COLOR=#ff0000][B])[/B][/COLOR] = [COLOR=#ff0000][B]Month([/B][/COLOR]Cells (2,12)[COLOR=#ff0000][B])[/B][/COLOR] then 
      Cells (4,2) = 1 
End If

 

[Rick Rothstein]

Hi

By using

=IF(TEXT($I18,"mmyy")=TEXT(L$2,"mmyy"),1,0)

I can perform a check of the two cells and even though one is formatted as Oct-14 an the other is 23/10/14

If you wanted to do it structured...

If Format(Range("I18").Value, "mmyy") = Format(Range("L2").Value, "mmyy") Then
  ' Month and year are the same
Else
  ' Month or year is different
End If

otherwise if you wanted to assign the condition to a variable, declare the variable (example name Result) as Boolean and do this...

Result = Format(Range("I18").Value, "mmyy") = Format(Range("L2").Value, "mmyy")

 

[ChrisBM]

In VBA you can use the Format ( expression, [ format ] ) to much the same effect.

For more information, I personally look at:
MS Excel: FORMAT Function with Strings (VBA)

For your purposes I suspect you want something like

If Format(Range, Short Date) = Format(Other Range, Short Date) Then
*CODE HERE
End If

 

[Rick Rothstein]

For your purposes I suspect you want something like

If Format(Range, Short Date) = Format(Other Range, Short Date) Then
*CODE HERE
End If

Given the OP's example formula, your suggestion would not work if the days were different.

 

[Drofidnas]

Thanks Joe and Rick. Exactly what I wanted. I was playing with Format but couldn't get the syntax right!

Many thanks indeed!