Excel treats 1/2 as a date instead of a fraction

[Robert Pearson]

Is there a way to paste the fraction 1/2 into a cell and have Excel treat it
like the value one-half (which it is) instead of the date Jan-2, (which it
is not)?

I paste stock market data into Excel each night, and I always have to
manually fix certain entries, such as 1/2, which Excel always interprets
incorrectly as a date.

Thanks.

[Patrick Molloy]

enter 0.5 but format it 'as halves' -see fraction

Patrick
Microsoft Excel MVP
________________

Robert Pearson <rober...@yahoo.com> wrote in message
news:ORlmmlYY$GA....@cppssbbsa02.microsoft.com...

[Jim Rech]

I don't know that there is a really good way to overcome this problem. One
possibility is to turn on Transition Formula Entry under Tools, Options,
Transition. This enters a pasted "1/2" as a formula =1/2, which is better
than the date you are now getting. You'd have to turn this off afterward
however if you wanted Excel to recognize real date entries as dates rather
than as 1-2-3 would, as fractions.

Another approach is to let Excel paste in fractions as dates but then use a
macro to repair the damage. This macro scans a selection and where it sees
a date tries to convert it to a decimal.

Sub DatesToDec()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
If IsDate(Cell.Value) Then
Cell.NumberFormat = "0.00"
Cell.Value = Month(Cell.Value) / Day(Cell.Value)
End If
Next
End Sub

--
Jim Rech
Excel MVP

[Ashley Sheridan]

Select the range or sheet you're working on, then go under Format --> Cells
to the Number tab and select Fractions. You'll probably have to do this
before you paste to keep all your numbers correct.

HTH!
AMS

[Dana DeLouis]

I am not sure how you are entering the data into the spreadsheet.
If you just want to enter 1/2 by hand, the technique is to enter a leading
0.
ie 0 1/2
This will enter .5, and display it as 1/2. Good Luck. Dana

[David McRitchie]

I suggested placing a 0 in front on another post, and I expect this
one has the same problem. This is what I got in email (different
person).

=========
The problem is, I don't enter the prices. They are returned from the
Merrill Lynch site by the Web Query. I have no control over the format
of the incoming data, as that is determined by the web site being
queried. Reformatting the data after it is in the spreadsheet has no
beneficial effect, as it is simply overwritten when I next Refresh
Data. Also, reformatting as a fraction merely returns the numeric
equivalent of the date, not of the original fraction.
=========

You can simulate by pasting this in and using Data --> Text to Columns

"BAA",1/8,"1/21/2000"
"BAB",1/4,"1/21/2000"
"BAC",3/8,"1/21/2000"
"BAD",3/32,"1/21/2000"
"BAE",7/256,"1/21/2000"

Using Jim Rech's macro in this thread looks like the best solution,
until noting that 3/32 will be treated as a date and convert to 3.0
with Jim's macro since the month is 3 and the day is 1 so
3/1 will be the result.

Could certainly use a number or fraction option in the text to columns,
and in the format.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

Dana DeLouis <da...@email.msn.com> wrote in message news:uG59quUZ$GA.258@cppssbbsa04...

[David McRitchie]

Correction fraction 3/32 is interpreted as 3/1/1932
so the day of the month really is 1 in March and not as 1
in April as I was thinking in Jim's macro
converting dates to decimal fractions using XL2000.

[dco...@cix.compulink.co.uk]

In article <ORlmmlYY$GA....@cppssbbsa02.microsoft.com>,
rober...@yahoo.com (Robert Pearson) wrote:

> Is there a way to paste the fraction 1/2 into a cell and have Excel
> treat it
> like the value one-half (which it is) instead of the date Jan-2, (which
> it
> is not)?
>

> Thanks.
>
>

Yes, easy: type 0 1/2 (compare 1 1/2 for 1.5)

David Colver
Operis Analysis Kit, used by professional financial modellers,
www.operis.com/oak.htm

[Dana DeLouis]

Hi David. I see that importing fractions is a little difficult. It looks
like Excel first tries to assume a date format in the M/D format, and if it
looks a little strange, (ie 3/32, or 7/256) then it assumes a M/Y format.
Its a great idea using ...

Cell.Value = Month(Cell.Value) / Day(Cell.Value)

but I get the same problem when excel assumes a M/Y format.

Perhaps a different approach would be to import it as text so Excel can not
do any unforeseen changes to the data. Then you can run your own macro. For
Excel 2000, maybe something along this line of code...

Sub TextToFraction()
Dim rng As Range
Dim V As Variant
For Each rng In Selection
V = Split(rng, "/")
rng = V(0) / V(1)
Next
Selection.NumberFormat = "###/###"
End Sub

Anyway, interesting problem. Dana

"David McRitchie" <DMcRi...@msn.com> wrote in message
news:uyf6NfVZ$GA.265@cpmsnbbsa04...

> > > Is there a way to paste the fraction 1/2 into a cell and have Excel
treat
> > it
> > > like the value one-half (which it is) instead of the date Jan-2,
(which it
> > > is not)?
> > >

[Tushar Mehta]

Following up on Dana [DeLouis]'s suggestion of importing as text and
then processing the data further, an (antested) alternative might be to
download the file as text and use the import text wizard to open it in
Excel. The wizard allows one to define the kind of data each field
contains. I do that for a variety of files that I import into Excel.
For recurring tasks, I record my actions the first time around and then
use the generated code as part of an automated process.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--
In <uUkdVibZ$GA....@cppssbbsa02.microsoft.com>, Dana DeLouis
<da...@email.msn.com> wrote

[David McRitchie]

Hi Robert, Patrick, Jim Rech, Dana, Tushar and Cecil

I think the following macro will help to convert stock prices
that were **pasted** from HTML into Excel, but that appear as
dates back to fractions. Color added to show a date
converted back to a fraction. The coding could probably be
cleaned up a bit. Should handle text, general, *dates*
You will have to subject it to your own data and testing.

The purpose here is to fixup data after the pasting since there
doesn't really seem to be much that can be done beforehand,
except for turning on transition formulas during cutting/pasting.

Perhaps test for current year should include test for previous
year with an OR.

Sub TextToFraction()
'David McRitchie 2000-01-24 Stock Prices back to fractions
'Excel treats 1/2 as a date instead of a fraction
'http://www.deja.com/=dnc/getdoc.xp?AN=574126457
Dim rng As Range 'r-n-g as in range

For Each rng In Selection

If rng.NumberFormat = "@" Then
If InStr(1, rng.Value, "/") Then
rng.NumberFormat = "# ###/###"
Else
rng.NumberFormat = "General"
End If
If Trim(rng.Formula) <> "" Then rng.Value = Trim(rng.Value)
Else
If IsDate(rng) Then
rng.NumberFormat = "[Color 30]# ###/###"
If Year(rng) = Year(Now()) Then 'i.e. 7/8
rng.Formula = "=" & Month(rng) & "/" & Day(rng)
ElseIf Year(rng) < 1900 Then 'i.e. 7/267 entered into General
rng.Value = "=" & Month(rng) & "/" & Year(rng)
Else 'i.e. 29/32 or 61/64
rng.Value = "=" & Month(rng) & "/" & (Year(rng) Mod 100)
End If
End If
End If
Next rng
End Sub

Some of simulated stock prices data tested, after text to data

"BAA",1/8,"1/21/2000"
"BAB",1/4,"1/21/2000"
"BAC",3/8,"1/21/2000"
"BAD",3/32,"1/21/2000"
"BAE",7/256,"1/21/2000"

Any ideas on why 7/267 entered into General is treated as a
date in this macro when dates in Excel aren't recognized before 1900?

The date formats that have to be converted back to fractions
are d-mmm and mmm-yy in my testing, and perhaps should be
used instead of IsDate().

HTH, This or a better one will appear on my stock page later on.

David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

rather than reposting the thread look at them in Deja News
Question posed by Robert Pearson 18 Jan 2000
http://www.deja.com/=dnc/getdoc.xp?AN=574126457 (see thread)
Also in thread as on track Patrick Molloy, Jim Rech, Dana DeLouis, Tushar Mehta
Cecil Bankston posed similar question 11Jan 2000
http://www.deja.com/=dnc/getdoc.xp?AN=571052937
there was a suggestion to turn on transition formulas for the
duration of the cutting and pasting.

[cmpul...@gmail.com]

On Monday, January 17, 2000 at 3:00:00 AM UTC-5, Robert Pearson wrote:

I had a similar issue with tracking a number of completed steps in a project (24 total steps in this case); here is what worked for me:

I created a table, into which I could paste my data as values only. Some fractions, such as 6/24 came out as a date (June 24), while others, such as 24/24 came as text. These two each needed to be calculated separately.

I created a new column on the right (titled "count") with a function referencing the fraction. Since any date will be greater than 1, I used =IF([@Current]>1,TEXT([@Current],"MM/DD"),[@Current]) - where @Current is the column with the offending dates/fractions.

This will only generate text, so to get it into a number format, I created another column with the function =LEFT(@count,2)/RIGHT(@count,2). They you'll have a number you can format as a decimal, fraction, percentage, etc.

This was the simplest way that I could figure that wouldn't require running macros or pasting through notepad.