Hi Topper,

I see that you preceded me with a revised solution and have used a similar
idea.

As you may have imagined, my original was developed using US regional
settings (on a UK system).

In consequence, the putative numerator and denominator orders were inverted
for UK settings: I assume that Bill realised this and niftily reversed the
relevant code lines. I think that your code needs to do likewise to avoid
(say) 1/9 being converted to 9/1. Of course, if Bill amended my code, I am
sure that he will have done similarly with yours.

It was thhis inversion that, incidentally, caused me to restrict my updated
solution to non-US date settings.

---
Regards,
Norman



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