.0 is creating #VALUE! Error

K

kidcasey13

I am currently using the following formula:

=(H21*60)-IF(MID(H21,FIND(".",H21,1),5)>0,(MID(H21,FIND(".",H21,1),5)*60),"")+(MID(H21,FIND(".",H21,1),5))

However, I get the results of #VALUE!

In cell H21, the value is 27.00

How do I force excel to recognize the .00 in the formula?

Thanks,
Ryan
 
S

Scoops

Hi Ryan

FIND is a string function, it's looking for text.

What result are you after?

Regards

Steve
 
D

Duke Carey

Are you trying to multiply the integer portion by 60 and add to that the
decimal portion?

=60*INT(H21)+MOD(H21,1)
 
P

Peo Sjoblom

What are you trying to do? Since you are multiplying with 60 I assume this
has something to do with times
If you have a decimal value for hours like 12.5 equaling 12 hours and 30
minutes you can convert it to excel time by multiplying with 24 and format
as time. If you have 12.5 and it means 12 hours and 50 minutes use INT(H21)
to extract hours and MOD(H21,1)*100 to extract minutes

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
B

Bob Phillips

Is this what you need?

=(H21*60)-IF(ISERROR(MID(H21,FIND(".",H21,1),5)>0),0,(MID(H21,FIND(".",H21,1
),5)*60))*2

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
K

kidcasey13

I am definitely working with times here. We work in quarter hours, so
..00, .25, .50, .75 (0 mins, 15 mins, 30 mins, 45 mins)

Avg hrs worked/week = 27.00

So I have 27.00 hours worked this week, however, I need to convert it
into minutes for a different formula (to determine how much data entry
is necessary per minute), therefore I'm taking the 27*60 to get the
number of minutes from hours, and adding the number of minutes to the
hours. So, 27*60=1620+Minutes (in this case, zero, which is throwing
the error).

In another example, 27.15=1620+20=1640 minutes.
 
B

Bob Phillips

So why not just

=H21*60


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
S

Scoops

Hi kidcasey13

I fail to understand your problem - why is 15 minutes not accurate for
a quarter hour?

Perhaps you should post the contents of your source cell and what you
expect to see in the calculated cell; using your formula 27.15 (text or
value) returns 1620.15 for me.

Regards

Steve
 
S

Scoops

Hi kidcasey13

I'm not understanding - why is 15 minutes not a quarter hour? I fail
to see how it could be any more accurate than that.

Using your formula above, 27.25 (whether text or value) returns 1620.25
for me, which I read as 1620 minutes and 15 seconds. Perhaps you
should post the contents of your source cell (27.25?) and what you
expect to see in your calculated cell.

Regards

Steve
 
Top