numbers contain hyphens to dates

[bill gras]

I have copied and pasted a file from a website into Excel 2000,in column "B"
there was a entry like this:-
11/14 which means 11th place from 14 positions. Excel shows this entry as
Nov-14 (a date). I have tried
every thing that I could find , but can not bring it back to 11/14 (which is
very important for my end result)
There are about 300 rows that I have to import every day.
Is there a worksheet function that you know of ?
Or is there a macro that would work?
Can you please help ?

hopeful Bill

--
bill gras

[Norman Jones]

Hi Bill,

To convert the spurious dates to fractional numbers, try::

Public Sub DatesToFractions()
Dim RCell As Range

For Each RCell In Selection '<<===== CHANGE
With RCell
If IsDate(.Value) Then
.Value = 0 & " " & Month(.Value) _
& "/" & Day(.Value)
End If
End With
Next

End Sub

---
Regards,
Norman



"bill gras" <billgras@discussions.microsoft.com> wrote in message
news:4C9BF329-6404-462F-8DC9-04AAA980C617@microsoft.com...
>I have copied and pasted a file from a website into Excel 2000,in column
>"B"
> there was a entry like this:-
> 11/14 which means 11th place from 14 positions. Excel shows this entry as
> Nov-14 (a date). I have tried
> every thing that I could find , but can not bring it back to 11/14 (which
> is
> very important for my end result)
> There are about 300 rows that I have to import every day.
> Is there a worksheet function that you know of ?
> Or is there a macro that would work?
> Can you please help ?
>
> hopeful Bill
>
> --
> bill gras

[bill gras]

Thank you very much for your time and input
--
bill gras


"Norman Jones" wrote:

> Hi Bill,
>
> To convert the spurious dates to fractional numbers, try::
>
> Public Sub DatesToFractions()
> Dim RCell As Range
>
> For Each RCell In Selection '<<===== CHANGE
> With RCell
> If IsDate(.Value) Then
> .Value = 0 & " " & Month(.Value) _
> & "/" & Day(.Value)
> End If
> End With
> Next
>
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "bill gras" <billgras@discussions.microsoft.com> wrote in message
> news:4C9BF329-6404-462F-8DC9-04AAA980C617@microsoft.com...
> >I have copied and pasted a file from a website into Excel 2000,in column
> >"B"
> > there was a entry like this:-
> > 11/14 which means 11th place from 14 positions. Excel shows this entry as
> > Nov-14 (a date). I have tried
> > every thing that I could find , but can not bring it back to 11/14 (which
> > is
> > very important for my end result)
> > There are about 300 rows that I have to import every day.
> > Is there a worksheet function that you know of ?
> > Or is there a macro that would work?
> > Can you please help ?
> >
> > hopeful Bill
> >
> > --
> > bill gras
>
>
>

[Norman Jones]

Hi Bill,

Or to convert the spurious dates to text fractions, try:

'=======================>>
Public Sub DatesToTextFractions()
Dim rCell As Range
Dim rng As Range

For Each rCell In Selection
With rCell
If IsDate(.Value) Then
.NumberFormat = "@"
.Value = Month(.Value) _
& "/" & Day(.Value)
End If
End With
Next

End Sub
'<<=======================

And, if the values to be converted were always in a specific column (say
column B) then, try:

'=======================>>
Public Sub DatesToTextFractions2()
Dim rCell As Range
Dim rng As Range
Const myColumn As String = "B" '<<==== CHANGE

With ActiveSheet
Set rng = Intersect(.UsedRange, _
Columns(myColumn))
End With

For Each rCell In rng
With rCell
If IsDate(.Value) Then
.NumberFormat = "@"
.Value = Month(.Value) _
& "/" & Day(.Value)
End If
End With
Next

End Sub
'<<=======================


---
Regards,
Norman



"bill gras" <billgras@discussions.microsoft.com> wrote in message
news:4C9BF329-6404-462F-8DC9-04AAA980C617@microsoft.com...
>I have copied and pasted a file from a website into Excel 2000,in column
>"B"
> there was a entry like this:-
> 11/14 which means 11th place from 14 positions. Excel shows this entry as
> Nov-14 (a date). I have tried
> every thing that I could find , but can not bring it back to 11/14 (which
> is
> very important for my end result)
> There are about 300 rows that I have to import every day.
> Is there a worksheet function that you know of ?
> Or is there a macro that would work?
> Can you please help ?
>
> hopeful Bill
>
> --
> bill gras

[Piranha]

Bill,
If you just want a key entry way of doing it.
Enter a zero and space before the fraction.
(0 7/8) will show as 7/8

Dave

Hi Bill,

Or to convert the spurious dates to text fractions, try:

'=======================>>
Public Sub DatesToTextFractions()
Dim rCell As Range
Dim rng As Range

For Each rCell In Selection
With rCell
If IsDate(.Value) Then
.NumberFormat = "@"
.Value = Month(.Value) _
& "/" & Day(.Value)
End If
End With
Next

End Sub
'<<=======================

And, if the values to be converted were always in a specific column (say
column B) then, try:

'=======================>>
Public Sub DatesToTextFractions2()
Dim rCell As Range
Dim rng As Range
Const myColumn As String = "B" '<<==== CHANGE

With ActiveSheet
Set rng = Intersect(.UsedRange, _
Columns(myColumn))
End With

For Each rCell In rng
With rCell
If IsDate(.Value) Then
.NumberFormat = "@"
.Value = Month(.Value) _
& "/" & Day(.Value)
End If
End With
Next

End Sub
'<<=======================


---
Regards,
Norman



"bill gras" <billgras@discussions.microsoft.com> wrote in message
news:4C9BF329-6404-462F-8DC9-04AAA980C617@microsoft.com...
>I have copied and pasted a file from a website into Excel 2000,in column
>"B"
> there was a entry like this:-
> 11/14 which means 11th place from 14 positions. Excel shows this entry as
> Nov-14 (a date). I have tried
> every thing that I could find , but can not bring it back to 11/14 (which
> is
> very important for my end result)
> There are about 300 rows that I have to import every day.
> Is there a worksheet function that you know of ?
> Or is there a macro that would work?
> Can you please help ?
>
> hopeful Bill
>
> --
> bill gras

[Norman Jones]

Hi Piranha,

> > There are about 300 rows that I have to import every day.

---
Regards,
Norman



"Piranha" <Piranha.1tl8qh_1123743931.0466@excelforum-nospam.com> wrote in
message news:Piranha.1tl8qh_1123743931.0466@excelforum-nospam.com...
>
> Bill,
> If you just want a key entry way of doing it.
> Enter a zero and space before the fraction.
> (0 7/8) will show as 7/8
>
> Dave
> Norman Jones Wrote:
>> Hi Bill,
>>
>> Or to convert the spurious dates to text fractions, try:
>>
>> '=======================>>
>> Public Sub DatesToTextFractions()
>> Dim rCell As Range
>> Dim rng As Range
>>
>> For Each rCell In Selection
>> With rCell
>> If IsDate(.Value) Then
>> .NumberFormat = "@"
>> .Value = Month(.Value) _
>> & "/" & Day(.Value)
>> End If
>> End With
>> Next
>>
>> End Sub
>> '<<=======================
>>
>> And, if the values to be converted were always in a specific column
>> (say
>> column B) then, try:
>>
>> '=======================>>
>> Public Sub DatesToTextFractions2()
>> Dim rCell As Range
>> Dim rng As Range
>> Const myColumn As String = "B" '<<==== CHANGE
>>
>> With ActiveSheet
>> Set rng = Intersect(.UsedRange, _
>> Columns(myColumn))
>> End With
>>
>> For Each rCell In rng
>> With rCell
>> If IsDate(.Value) Then
>> .NumberFormat = "@"
>> .Value = Month(.Value) _
>> & "/" & Day(.Value)
>> End If
>> End With
>> Next
>>
>> End Sub
>> '<<=======================
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "bill gras" <billgras@discussions.microsoft.com> wrote in message
>> news:4C9BF329-6404-462F-8DC9-04AAA980C617@microsoft.com...
>> >I have copied and pasted a file from a website into Excel 2000,in
>> column
>> >"B"
>> > there was a entry like this:-
>> > 11/14 which means 11th place from 14 positions. Excel shows this
>> entry as
>> > Nov-14 (a date). I have tried
>> > every thing that I could find , but can not bring it back to 11/14
>> (which
>> > is
>> > very important for my end result)
>> > There are about 300 rows that I have to import every day.
>> > Is there a worksheet function that you know of ?
>> > Or is there a macro that would work?
>> > Can you please help ?
>> >
>> > hopeful Bill
>> >
>> > --
>> > bill gras
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=394826
>

[Piranha]

Norman,
See thats why you are the brain, i missed that

P.S. Norman, may i email you about that workbook you fixed?
I still have your address, if its Ok.
Dave

Hi Piranha,

> > There are about 300 rows that I have to import every day.

---
Regards,
Norman



"Piranha" <Piranha.1tl8qh_1123743931.0466@excelforum-nospam.com> wrote in
message news:Piranha.1tl8qh_1123743931.0466@excelforum-nospam.com...
>
> Bill,
> If you just want a key entry way of doing it.
> Enter a zero and space before the fraction.
> (0 7/8) will show as 7/8
>
> Dave
> Norman Jones Wrote:
>> Hi Bill,
>>
>> Or to convert the spurious dates to text fractions, try:
>>
>> '=======================>>
>> Public Sub DatesToTextFractions()
>> Dim rCell As Range
>> Dim rng As Range
>>
>> For Each rCell In Selection
>> With rCell
>> If IsDate(.Value) Then
>> .NumberFormat = "@"
>> .Value = Month(.Value) _
>> & "/" & Day(.Value)
>> End If
>> End With
>> Next
>>
>> End Sub
>> '<<=======================
>>
>> And, if the values to be converted were always in a specific column
>> (say
>> column B) then, try:
>>
>> '=======================>>
>> Public Sub DatesToTextFractions2()
>> Dim rCell As Range
>> Dim rng As Range
>> Const myColumn As String = "B" '<<==== CHANGE
>>
>> With ActiveSheet
>> Set rng = Intersect(.UsedRange, _
>> Columns(myColumn))
>> End With
>>
>> For Each rCell In rng
>> With rCell
>> If IsDate(.Value) Then
>> .NumberFormat = "@"
>> .Value = Month(.Value) _
>> & "/" & Day(.Value)
>> End If
>> End With
>> Next
>>
>> End Sub
>> '<<=======================
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "bill gras" <billgras@discussions.microsoft.com> wrote in message
>> news:4C9BF329-6404-462F-8DC9-04AAA980C617@microsoft.com...
>> >I have copied and pasted a file from a website into Excel 2000,in
>> column
>> >"B"
>> > there was a entry like this:-
>> > 11/14 which means 11th place from 14 positions. Excel shows this
>> entry as
>> > Nov-14 (a date). I have tried
>> > every thing that I could find , but can not bring it back to 11/14
>> (which
>> > is
>> > very important for my end result)
>> > There are about 300 rows that I have to import every day.
>> > Is there a worksheet function that you know of ?
>> > Or is there a macro that would work?
>> > Can you please help ?
>> >
>> > hopeful Bill
>> >
>> > --
>> > bill gras
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=394826
>

[Norman Jones]

Hi Dave,

That would be fine!


---
Regards,
Norman



"Piranha" <Piranha.1tleaf_1123751134.6666@excelforum-nospam.com> wrote in
message news:Piranha.1tleaf_1123751134.6666@excelforum-nospam.com...
>
> Norman,
> See thats why you are the brain, i missed that
>
> P.S. Norman, may i email you about that workbook you fixed?
> I still have your address, if its Ok.
> Dave
> Norman Jones Wrote:
>> Hi Piranha,
>>
>> > > There are about 300 rows that I have to import every day.
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Piranha" <Piranha.1tl8qh_1123743931.0466@excelforum-nospam.com> wrote
>> in
>> message news:Piranha.1tl8qh_1123743931.0466@excelforum-nospam.com...
>> >
>> > Bill,
>> > If you just want a key entry way of doing it.
>> > Enter a zero and space before the fraction.
>> > (0 7/8) will show as 7/8
>> >
>> > Dave
>> > Norman Jones Wrote:
>> >> Hi Bill,
>> >>
>> >> Or to convert the spurious dates to text fractions, try:
>> >>
>> >> '=======================>>
>> >> Public Sub DatesToTextFractions()
>> >> Dim rCell As Range
>> >> Dim rng As Range
>> >>
>> >> For Each rCell In Selection
>> >> With rCell
>> >> If IsDate(.Value) Then
>> >> .NumberFormat = "@"
>> >> .Value = Month(.Value) _
>> >> & "/" & Day(.Value)
>> >> End If
>> >> End With
>> >> Next
>> >>
>> >> End Sub
>> >> '<<=======================
>> >>
>> >> And, if the values to be converted were always in a specific column
>> >> (say
>> >> column B) then, try:
>> >>
>> >> '=======================>>
>> >> Public Sub DatesToTextFractions2()
>> >> Dim rCell As Range
>> >> Dim rng As Range
>> >> Const myColumn As String = "B" '<<==== CHANGE
>> >>
>> >> With ActiveSheet
>> >> Set rng = Intersect(.UsedRange, _
>> >> Columns(myColumn))
>> >> End With
>> >>
>> >> For Each rCell In rng
>> >> With rCell
>> >> If IsDate(.Value) Then
>> >> .NumberFormat = "@"
>> >> .Value = Month(.Value) _
>> >> & "/" & Day(.Value)
>> >> End If
>> >> End With
>> >> Next
>> >>
>> >> End Sub
>> >> '<<=======================
>> >>
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>> >> "bill gras" <billgras@discussions.microsoft.com> wrote in message
>> >> news:4C9BF329-6404-462F-8DC9-04AAA980C617@microsoft.com...
>> >> >I have copied and pasted a file from a website into Excel 2000,in
>> >> column
>> >> >"B"
>> >> > there was a entry like this:-
>> >> > 11/14 which means 11th place from 14 positions. Excel shows this
>> >> entry as
>> >> > Nov-14 (a date). I have tried
>> >> > every thing that I could find , but can not bring it back to
>> 11/14
>> >> (which
>> >> > is
>> >> > very important for my end result)
>> >> > There are about 300 rows that I have to import every day.
>> >> > Is there a worksheet function that you know of ?
>> >> > Or is there a macro that would work?
>> >> > Can you please help ?
>> >> >
>> >> > hopeful Bill
>> >> >
>> >> > --
>> >> > bill gras
>> >
>> >
>> > --
>> > Piranha
>> >
>> ------------------------------------------------------------------------
>> > Piranha's Profile:
>> > http://www.excelforum.com/member.php...o&userid=20435
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=394826
>> >
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=394826
>