8/8 evaluated as DATE, Need $/$

[MarkBeck]

Hi

I have a range that has as part of a string, values such as 6/8, meaning used 6 from 8. NOT June 8th. However when i extract this date (via a MID function) it returns the DATE value.

How can i alter my fomula to tell it that, at source, this number is a FRACTION rather then a DATE.

Thank you

Mark

 

[SkipVought]

faq68-5827

Format the range as fraction and THEN load the data.



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

On second thought, Format as TEXT before you load.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[MarkBeck]

Thanks Skip

I tried all that to no avail. The problem is (as well described in the link) that you are dealing with a totally new value once MID() enters the fray.

In the FAQ i dont see a solution. Other then what you wrote, is there anything elese you can think of?

Much appreciated

Mark

 

[bluedragon2]

Not sure why you are getting a date from using the MID function on a text string.

If I have in a cell: Today I have 6/8 left
And in another cell I put: =MID(A1,14,3)

I get the text string 6/8 as a result.

What is your original data that you are using the MID function on and what is the cell formated before you apply the MID function?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Please post an Actual Example of the data in the cell AND your formula that is producing the unexpected results.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[MarkBeck]

A1; cavities: 6/8
A2; (formula)


=IF(ISNUMBER(FIND(":",A1,1)),TRIM(MID(A1,FIND(":",A1,1)+1,100))*1,"")

I guess the "*1" makes that difference. But i need the "6/8" converted to 0.75 rather then the DATEVALUE 39668.

 

[AnotherHiggins]

The reason it is being converted to a number - and therefor text - is because you are multiplying the result by 1.

Take out the part in red:

=IF(ISNUMBER(FIND(":",A1,1)),TRIM(MID(A1,FIND(":",A1,1)+1,100))[!]*1[/!],"")

You'll be left with:
=IF(ISNUMBER(FIND(":",A1,1)),TRIM(MID(A1,FIND(":",A1,1)+1,100)),"")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Grr.

[tab]"- and therefor text -"
should read
[tab]"- and therefore a date -"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[MarkBeck]

But i need the "6/8" converted to 0.75 rather then the DATEVALUE 39668. I need to use that number.

How do i do that?

 

[bluedragon2]

=IF(ISNUMBER(FIND(":",A1,1)),VALUE(TRIM(MID(A1,FIND(":",A1,1)+1,100))),"")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

I see your point, MarkBeck. Even Blue's code is still returning 39607 instead of 0.75.

I think that, in order to make Excel perform the calculation, you'll need to pull each number separately in two different sections of a larger formula. It will end up looking something like:
[tab]=(formula to find numerator)/(formula to find denominator)

I don't suppose the numbers will always be single-digits, will they?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[bluedragon2]

Yeah, saw that too, so came up with:

=IF(ISNUMBER(FIND(":",A1,1)),MID(A1,FIND(":",A1,1)+2,FIND("/",A1)-(FIND(":",A1)+2))/MID(A1,FIND("/",A1)+1,LEN(A1)-FIND("/",A1)),"")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[MarkBeck]

Thanks Dragon

Only adds a intiger to the date.

If i just put in a cell "+6/8" it returns the fraction. I have tried various methods of getting a text string of +6/8 but it returns VALUE#

 

[MarkBeck]

Bang on Dragon!

Nothing replaces just simple common sense!

 

[AnotherHiggins]

Blue's formula worked for me with your previously posted example (cavities: 6/8).

Did you try it with that entry, or something else? If the data you are evaluating changes, it is important to let us know....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

[tt]
=6/8
[/tt]
also returns .75

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[GlennUK]

As it's a fraction, make it a proper fraction string before converting to numeric with *1 ...

=IF(ISNUMBER(FIND(":",A1,1)),("0 "&TRIM(MID(A1,FIND(":",A1,1)+1,100)))*1,"")

( by sticking "0 " in front of it )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[MarkBeck]

Thanks GlennUK! That littkle subtlety does the trick.

Mark
ExPat

 

[yogia]

Hi MarkBeck:

How about ...

=("0"&MID(A1,FIND(":",A1)+1,255))*1

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com