Hi Alfredo

The reason why it was not working is basically the variable type mismatch.
MyReal month from inpubox is returned as a string which you need to convert
to numeric before converting....or you need to declaare the variables in the
first place.....Anyway I have modified your code to work. Use IIF function()
which reduce a bith of lines.....

mydate = DateValue(Range("a7"))
mymonth = CInt(Month(mydate))
myday = CInt(Day(mydate))
MyYear = Year(mydate)
myrealmonth = CInt("0" & InputBox("Enter the month you are working with",
"MONTH"))
MyRealDate = DateSerial(MyYear, myrealmonth, IIf(mymonth < myrealmonth,
mymonth, myday))

If this post helps click Yes
---------------
Jacob Skaria


"Alfredo_CPA" wrote:

I like the idea. I'm trying to put the idea in VBA now.
I have this so far (I will loop it for selected range once it works):

MyDate = DateValue(Range("a7"))
MyMonth = Month(DateValue(Range("a7")))
MyDay = Day(DateValue(Range("a7")))
MyYear = Year(DateValue(Range("a7")))
MyRealMonth = InputBox("Enter the month you are working with", "MONTH")
If MyMonth < MyRealMonth Then MyRealDay = MyMonth Else
MyRealDay = MyDay
ActiveCell.Value = MyRealMonth & "/" & MyRealDay & "/" & MyYear

This part of my code is not working. I'm having a problem with the if to
asign the day:
If MyMonth < MyRealMonth Then MyRealDay = MyMonth Else
MyRealDay = MyDay

Thanks

"Jacob Skaria" wrote:

Alfredo, missed few points here.

1. This will work only if ColA dates are in excel date format.
2. Once you apply the formula format Col B to a date format of your choice.

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

If you are sure that all the dates are of month May try the below formula
(not VBA code) in the column near to that...

Suppose you have the below data in Col A in B1 enter the formula and copy
down as required

=DATE(YEAR(A1),5,IF(MONTH(A1)<5,MONTH(A1),DAY(A1) ))

If this post helps click Yes
---------------
Jacob Skaria


"Alfredo_CPA" wrote:

I'm wondering if there is a way with VBA to correc dates that look like this
(all of them are MAY dates). Is not a format issue as they look typed like
that:
10/5/2009
1/05/2009
04/5/2009
1/5/09
5/10/09
05/10/2009
5/1/09
5/01/09
etc

--
Thanks