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

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

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

try this UDF
in the spreadsheet you'd enter
=CorrectedDate(B6,5) where B6 is a date like your 5/10/2009 and 5 is the
correct month --- means you can use this for june, july etc
the answer will be an excel date, so format the cell appropriately like
"dd-mmm-yy" or whatever

Option Explicit
Function CorrectedDate(text As String, mnth As Long) As Date
' input v1/v2/yyyy
' where either v1 or v2 is correct month
Dim dte As Variant ' becomes a vector with 3 values
dte = Split(text, "/") ' dte becomes a vector with 3 values

If dte(0) = mnth Then
' text is mm/dd/yy
CorrectedDate = DateSerial(dte(2), dte(0), dte(1))
Else
' text is dd/mm/yy
CorrectedDate = DateSerial(dte(2), dte(1), dte(0))
End If
End Function




"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