Date Formatting

[Guest]

I have two cells containing dates in the format of 07/14/05. I can use
Selection.NumberFormat = "m/yy;@" to change the format of the cells to month
and year. However, Excel still uses the original serial number from
07/14/05. I am trying to compare these two cells and delete any transactions
that did not take place in the same month, but until I figure this out I can
only capture the transactions in the same day. Does anyone have any
suggestions?

 

[Guest]

i know with vba you can get the name of the month by

monthname(month(date)) ' will give you the present month
' date being the equivalent of Today()
' or replace date by the value in your cell


hth

J

 

[Bob Phillips]

So juts test the month and year of the date, formatting only changes the
value seen, not the underlying value.

 

[Guest]

This is the code I am using for the test. How would I modify it to test only
the month and year as you suggested? Thank you in advance.

Range("b2").Select

Do Until ActiveCell.Value = ""
If Range("A2") <> Range("E2") Then
ActiveCell.Rows(1).EntireRow.Select
ActiveCell.Rows(1).EntireRow.Delete
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

 

[Guest]

Thank you. That get's me closer. However, one colomn of data is the result
of a query. I could not get this to change that.

 

[Losse]

For deleting the dates that are not in the same month do:

Sub rReplace()
Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = #/* Then
Call rReplace
Else Selection.EntireRow.Delete
Loop


Where "#" is the month you wish to keep.

 

[Bob Phillips]

Not sure as I do not understand your code (it does the same test regardless
of the activecell!), but it would be something along the lines of

If Month(Range("A2").Value) <> Month(Range("E2")).Value) Then