Thanks Toppers , for your help and your imput
I hope your cricket team does well
--
bill gras


"Toppers" wrote:

Bill,
FYI: I looked at your problem here in the UK (dates are DD/MM/YY)
and the entries of 3/15 and 4/13 were interpretted by Excel as 01/03/2015
and 01/04/2013 and therefore gave results of 3/1 and 4/1 (month/day).

The Excel logic iapparently is if the month is not valid, make 'month' the
YEAR and accordingly make the 'day' value the MONTH with default of '01' for
the day.

I have taken the liberty of amending Norman's code which now works OK on my
UK data: give it a try.


Public Sub DatesToTextFractions()
Dim rCell As Range
Dim rng As Range
Range("a1:a5").Select
For Each rCell In Selection
rCell.Offset(0, 1) = "'" & rCell
With rCell
If IsDate(.Value) Then
.NumberFormat = "@"
If Year(.Value) < Year(Now()) Then
.Value = Month(.Value) _
& "/" & Right(Year(.Value), 2)
Else
.Value = Month(.Value) _
& "/" & Day(.Value)
End If
End If

End With
Next

End Sub


"bill gras" wrote:

Hi Norman
I used your macro (convert spurious dates to text fractions for column "B")
It works great with one exception , when the file shows 3/15 and 4/13 before
the import , then after the use of the macro yhe file shows 1/3 and 1/4
eg:- before import after import after using macro
1/9 1-sep 1/9
11/12 11-dec 11/12
3/15 mar-15 1/3
6/12 6-dec 6/12
4/13 april-13 1/4
The problem is in line 3 and 5

Can you still help with that ?

Thank you

--
bill gras