Thanks Rick ,it works perfect.
Just for my VBA training: will you please take a look of the code I wrote in
my last reply to Jacob and let me know where is the issue?

Thanks, I really appreciate your help

"Rick Rothstein" wrote:

Give this macro a try...

Sub ConvertDatesToStandardFormat()
Dim D As Date
Dim C As Range
Dim Answer As String
Answer = InputBox("What month number should the dates be?", "Get Month")
If Answer < "" Then
If IsNumeric(Answer) Then
For Each C In Selection
If C.Value < "" Then
D = CDate(C.Value)
If Month(D) < Answer Then
D = DateSerial(Year(D), Day(D), Month(D))
End If
C.NumberFormat = "General"
C.Value = D
End If
Next
End If
End If
End Sub

The macro starts off by asking you for the month **number** that the months
should be and then changes all entries (whether they are text or real dates
to start off with) into real dates, so you can format the column as you wish
(or, if you tell me what format the dates should be displayed as, I can make
the macro do the formatting automatically).

Note: The macro will be fooled by 2-digit years which are not at the end of
a text entry. For example, if this is in a cell formatted as Text...

09/05/07

where the 09 is meant to be the year, the macro will not be able to
determine that.

--
Rick (MVP - Excel)


"Alfredo_CPA" .(donotspam) wrote in message
...
I know for sure the month is May as I receive a monthly file with just one
month data, but for some reason I Ireceive it with the dates in all the
ways
I decribed in my original posting i.e. next month I will have the same
problem with Jun data (06/01/09, 02/06/09, 3/06/09, etc - all will be
June
dates)
Thanks


"Rick Rothstein" wrote:

If you can have both 10/5/2009 and 05/10/2009 on the same sheet
(column?),
then no, a macro will not be able to figure out (on its own) that May was
meant for both. You could write a macro that would ask you for the month
and
have the code use that information to straighten things out, but this
would
require you to select **all** the May dates manually before running the
macro. Would that be an acceptable solution? Also, are these values
formatted as Text or do they show as real Excel Dates in the cells?

--
Rick (MVP - Excel)


"Alfredo_CPA" .(donotspam) wrote in message
...
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