HI.

How do you change a formula to read the value as a result of a formula
and not necessarily as a real number?

Example:

In G6, i have the following formula that gives me the hour of a time
in F6

=IF(F6>0,MOD(F6-"1:00",1),"")

F6 contains 00:30:08, The result is 23

In another cell, I am using this formula, but it doesnt recognize the
23.

=SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23"))

The 23 is actually the HOUR of time so it isn't a whole number or real
number persay. It is a rounded time to the hour.

Yes, but it isn't a text value so putting quotes around it in your SP
formula won't work. Try it like this:

=SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331=23/24))

as Excel stores times as fractions of a 24-hour day.

Hope this helps.

Pete

On Jul 24, 4:15*pm, "J.W. Aldridge" <jeremy.w.aldri...@gmail.com>
wrote:
> HI.
>
> How do you change a formula to read the value as a result of a formula
> and not necessarily as a real number?
>
> Example:
>
> In G6, i have the following formula that gives me the hour of a time
> in F6
>
> =IF(F6>0,MOD(F6-"1:00",1),"")
>
> F6 contains 00:30:08, *The result is 23
>
> In another cell, I am using this formula, but it doesnt recognize the
> 23.
>
> =SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23"))
>
> The 23 is actually the HOUR of time so it isn't a whole number or real
> number persay. It is a rounded time to the hour.

First of all this part

--(G6:G58331="23"))

looks for a text representation of 23, remove the quotations
if G6:G58331 have numbers. Secondly your formula

=IF(F6>0,MOD(F6-"1:00",1),"")

returns

23:30 and furthermore it is a time value so the real number is

0.979259259259259

so if you have 23 in the range you are testing for it will obviously not be
a match

If you have integers in G6:G58331 then you can use


=--TEXT(IF(F6>0,MOD(F6-"1:00",1),""),"h")




--


Regards,


Peo Sjoblom

"J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message
news:e80b9f1f-3bd4-4f2d-8a4e-d5158e3bf399@c58g2000hsc.googlegroups.com...
> HI.
>
> How do you change a formula to read the value as a result of a formula
> and not necessarily as a real number?
>
> Example:
>
> In G6, i have the following formula that gives me the hour of a time
> in F6
>
> =IF(F6>0,MOD(F6-"1:00",1),"")
>
> F6 contains 00:30:08, The result is 23
>
> In another cell, I am using this formula, but it doesnt recognize the
> 23.
>
> =SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23"))
>
> The 23 is actually the HOUR of time so it isn't a whole number or real
> number persay. It is a rounded time to the hour.

Got info from both suggestions....
Working purfectly now.

Thanx!