# Thread: =value function (2000 SR1)

1. ## =value function (2000 SR1)

I have a spreadsheet that I've used for years that uses the =value(xx). When cell xx (formatted as text) contains 3/4, the number 0.75 is returned, change to 1/2 & 0.5 is returned, etc. In attempting to use this same procedure in an new spreadsheet, the number 31319 is returned. Can anyone tell me what's going on & how to fix it?
Thanks, Scott

2. ## Re: =value function (2000 SR1)

Could the formual be pinting to a cell that contains a Date. 31319 when formatted as a date = Sept 29 1985.

If the cell pointed to does not contain a date, what exactly does it contain ?

Andrew C.

3. ## Re: =value function (2000 SR1)

No, the cell is formatted as "General".
If it helps, I think that the original spreadsheet was created in Excel97.
BTW, the cell returns 37319, not 31319 as I stated earlier.
Scott

4. ## Re: =value function (2000 SR1)

It is even more interesting if t returns 37319, which is March 4 2002, which could display as 3/4 depending on the formatting. Could you say what value is in the cell XX, where =VALUE(XX) returns 37319.

Andrew C

5. ## Re: =value function (2000 SR1)

Yes, xx contains 3/4.
My first assumption was that somehow I had globally formatted the template (or whatever you call the way a new spreadsheet opens) to read things entered with slashes as dates, but I can't find anything that indicates that that is the case, nor can I find anyway to change it.
Scott

6. ## Re: =value function (2000 SR1)

I'm afraid that you will probably have to re-enter your data, but before doing so format the cell as Fraction (quarters), then entering 3/4 should return 0.75. I suspect that the existing 3/4 is actually text, and is being interpreted by excel as March 4 of the current year. If you enter =VALUE("3/4") in a cell do you get the same result ?

Andrew C

7. ## Re: =value function (2000 SR1)

Yes, if I enter =value("3/4"), it returns 37319. The question is, can I get back to the old way of doing things? The original spreadsheet works just fine, even if I make modifications to it. If I make a copy of the original, or if I start from scratch, it won't work. Very frustrating.
Scott

8. ## Re: =value function (2000 SR1)

If you enter 3/4 into a cell in XL2K that is formatted General, it will be interpreted as march the 4th of the current year 2002. That would store 37319 into the cell. Using the value function on a text cell containing 3/4 will do the same conversion.

What do you want to happen? If you are trying to get .75 as a result, then you can use a formula like this one if the 3/4 is in cell C1:

<pre>=LEFT(C1,FIND("/",C1)-1)/RIGHT(C1,LEN(C1)-FIND("/",C1))
</pre>

9. ## Re: =value function (2000 SR1)

I understand, but how can you get it to stop returning a date? I've tried resetting the entire spreadsheet to text format and then reformatting the cell with the =value function to various other formats besides General, but it always returns 37319! The original spreadsheet, written in Excel97 I think, returns 0.75, i.e. the numeric value. Do you know of any way to stop Excel2K from returning dates?
Scott

10. ## Re: =value function (2000 SR1)

As far as I know, there is no way to change the way XL decides what the value is.

11. ## Re: =value function (2000 SR1)

>but how can you get it to stop returning a date?
<img src=/w3timages/blackline.gif width=33% height=2>

Andrew's solution of preformatting the entry area as Fraction works for me.

12. ## Re: =value function (2000 SR1)

After getting John's reply, I reread your post - I had overlooked the recommendation to format the cell "Fraction". Works just like it should now!! THANK YOU.
Scott

13. ## Re: =value function (2000 SR1)

Thank you John. I reread Andrew's reply, I had missed the part about formatting the cell to Fraction. Works just like I need it to now.
Scott <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•