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?

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


"Dave M." wrote:

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?

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

--
HTH

Bob Phillips

"Dave M." <Dave wrote in message
...
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?

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"Dave M." <Dave wrote in message
...
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?

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.

"Gixxer_J_97" wrote:

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


"Dave M." wrote:

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?

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.


--
Losse
------------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=387615

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

--
HTH

Bob Phillips

"Dave M." <Dave wrote in message
...
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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"Dave M." <Dave wrote in message
...
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?