numbers contain hyphens(norman jones)
 

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

numbers contain hyphens(norman jones)
 

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

numbers contain hyphens(norman jones)
 

Hi Bill,

I assume that you are using dd/mm/yy type regional date settings.

Two possible, solutions:

Adopt US citizenship, move to the US and use proper dates

or, use the following kludge:

Public Sub DatesToTextFractions()
Dim rCell As Range
Dim rng As Range
Dim sStr1 As String, sStr2 As String

For Each rCell In Selection
With rCell
If IsDate(.Value) Then
If Year(.Value) < 2012 Then
sStr1 = Day(.Value)
sStr2 = Month(.Value)
Else
sStr1 = Month(.Value)
sStr2 = Year(.Value) - 2000
End If

.Value = "'" & sStr1 & "/" & sStr2

End If
End With
Next

End Sub


Note: This will only work for dd/mm/yy type date settings and will only work
until 2013. Poat back then for an updated solution.

---
Regards,
Norman


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

numbers contain hyphens(norman jones)
 

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

numbers contain hyphens(norman jones)
 

Norman,
You're right. _ I did get the reversed data but assumed (and
I know one shouldn't!) that Bill had accounted for US format. If only we all
used the ISO standard for dates! What never cease to surprise mei s how much
code effort is spent manipulating dates (me included!).

"Norman Jones" wrote:

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

numbers contain hyphens(norman jones)
 

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