numbers contain hyphens to dates
 

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

numbers contain hyphens to dates
 

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

numbers contain hyphens to dates
 

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

numbers contain hyphens to dates
 

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

numbers contain hyphens to dates
 

Hi Piranha,

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

---
Regards,
Norman



"Piranha" wrote in
message ...

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

numbers contain hyphens to dates
 

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

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


--
Piranh
-----------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043
View this thread: http://www.excelforum.com/showthread.php?threadid=39482

numbers contain hyphens to dates
 

Hi Dave,

That would be fine!


---
Regards,
Norman



"Piranha" wrote in
message ...

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

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

numbers contain hyphens to dates
 

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