software

I am currently using the following formula:

=(H21*60)-IF(MID(H21,FIND(quot;.quot;,H21,1),5)gt;0,(MID(H21,FIND(quot;.quot;, H21,1),5)*60),quot;quot;) (MID(H21,FIND(quot;.quot;,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,
RyanHi Ryan

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

What result are you after?

Regards

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

=60*INT(H21) MOD(H21,1)quot;kidcasey13quot; wrote:

gt; I am currently using the following formula:
gt;
gt; =(H21*60)-IF(MID(H21,FIND(quot;.quot;,H21,1),5)gt;0,(MID(H21,FIND(quot;.quot;, H21,1),5)*60),quot;quot;) (MID(H21,FIND(quot;.quot;,H21,1),5))
gt;
gt; However, I get the results of #VALUE!
gt;
gt; In cell H21, the value is 27.00
gt;
gt; How do I force excel to recognize the .00 in the formula?
gt;
gt; Thanks,
gt; Ryan
gt;
gt;

Hi Ryan,

instead of quot;H21quot;, use quot;TEXT(H21,quot;0.00quot;)quot;

--
Kind regards,

Niek Otten

quot;kidcasey13quot; gt; wrote in message ups.com...
gt;I am currently using the following formula:
gt;
gt; =(H21*60)-IF(MID(H21,FIND(quot;.quot;,H21,1),5)gt;0,(MID(H21,FIND(quot;.quot;, H21,1),5)*60),quot;quot;) (MID(H21,FIND(quot;.quot;,H21,1),5))
gt;
gt; However, I get the results of #VALUE!
gt;
gt; In cell H21, the value is 27.00
gt;
gt; How do I force excel to recognize the .00 in the formula?
gt;
gt; Thanks,
gt; Ryan
gt;
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

quot;kidcasey13quot; gt; wrote in message ups.com...
gt;I am currently using the following formula:
gt;
gt; =(H21*60)-IF(MID(H21,FIND(quot;.quot;,H21,1),5)gt;0,(MID(H21,FIND(quot;.quot;, H21,1),5)*60),quot;quot;) (MID(H21,FIND(quot;.quot;,H21,1),5))
gt;
gt; However, I get the results of #VALUE!
gt;
gt; In cell H21, the value is 27.00
gt;
gt; How do I force excel to recognize the .00 in the formula?
gt;
gt; Thanks,
gt; Ryan
gt;Is this what you need?

=(H21*60)-IF(ISERROR(MID(H21,FIND(quot;.quot;,H21,1),5)gt;0),0,(MID(H2 1,FIND(quot;.quot;,H21,1
),5)*60))*2

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;kidcasey13quot; gt; wrote in message ups.com...
gt; I am currently using the following formula:
gt;
gt;
=(H21*60)-IF(MID(H21,FIND(quot;.quot;,H21,1),5)gt;0,(MID(H21,FIND(quot;.quot;, H21,1),5)*60),quot;quot;
) (MID(H21,FIND(quot;.quot;,H21,1),5))
gt;
gt; However, I get the results of #VALUE!
gt;
gt; In cell H21, the value is 27.00
gt;
gt; How do I force excel to recognize the .00 in the formula?
gt;
gt; Thanks,
gt; Ryan
gt;
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.So why not just

=H21*60--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;kidcasey13quot; gt; wrote in message oups.com...
gt; I am definitely working with times here. We work in quarter hours, so
gt; .00, .25, .50, .75 (0 mins, 15 mins, 30 mins, 45 mins)
gt;
gt; Avg hrs worked/week = 27.00
gt;
gt; So I have 27.00 hours worked this week, however, I need to convert it
gt; into minutes for a different formula (to determine how much data entry
gt; is necessary per minute), therefore I'm taking the 27*60 to get the
gt; number of minutes from hours, and adding the number of minutes to the
gt; hours. So, 27*60=1620 Minutes (in this case, zero, which is throwing
gt; the error).
gt;
gt; In another example, 27.15=1620 20=1640 minutes.
gt;
Hi kidcasey13

Are you absolutely sure?

27.15 hours = 1620 9 = 1629 minutes

But that's not a quarter hour as you've stated you are using, nor is 20
minutes a quarter hour. What you'd expect to see for quarter hours is:

0.0 = 0
0.25 = 15
0.5 = 30
0.75 = 45

Bob's H21*60 seems eminently sensible and sufficient to me even if your
time is a text entry.

Regards

Steve27.00*60 = 1620
27.25*60 = 1635

Understandably it is only a difference of 15 quot;minutesquot;, however that is
still not accurate. (I realized I had a typing error above and put .15
instead of .25)

I have even rethought part of my formula, to this:

=(INT(H147)*60) (MID(H138,FIND(quot;.quot;,H147,1),5))

the INT works great, however because excel doesn't recognize the quot;.00quot;,
it's giving me #VALUE!

Ultimately, I'd like my formula to recognize that if there is a .25,
..50 or .75 to calculate the above way; but if not, to calculate just by
=INT(H147*60); I've tried using the quot;IFquot; amp; quot;MIDquot; together, but it's not
recognizing the quot;.00quot;

Still looking for help...