IF Statements and Time
 

Hello all,

I am having a bit of trouble with trying to use the IF Statement in
Excel when it comes to TIME.

This is what I am trying to do:

In Cell G3, it calculates minutes elapsed by using the following:

=TEXT(D3-C3, "h:mm:ss")

In Cell H3, I am trying to make it so that if Cell G3 is more than 7
Minutes, it writes "NO", and if the same cell is Less or Equal to 7, it
writes "YES"

Here is what I have in Cell H3:

=IF('7 or Less Articles'!G37,"No",IF('7 or Less
Articles'!G3<=7,"Yes"))

To make a long story short, it's not calculating properly.

Cell D3 is 9:03
Cell D3 is 9:00

9:03 minus 9:00 is 3 minutes, therefore Cell H3 should say "YES" -
instead it's saying "NO" I've been at this for several days trying to
find a solution.

I will appreciative of any help someone can provide.

IF Statements and Time
 

Hi Roland,

While I have not tested it - I think the problem may be that Cell G3 is
formatted as text. Excel cannot calculate numerical functions on a text
string.

Try creating a cell that has -

=VALUE(G3)

This should return a serial value for the time. Then just change the
formatting of the cell.

Then in H3 change the references to G3, to the new cell you have
created.

I don't think you will need to....but you may need to get the serial
value of '7' - and with the formula in H3 use the serial value instead
of the number '7'.

Hope this helps.

Regards

Rob


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=539482

IF Statements and Time
 

Sorry mate...rethought my original post.

In your IF statement in H3...just put inverted commas either side of
7....

eg IF('7 or Less Articles'!G3"7","No",IF('7 or Less
Articles'!G3<="7","Yes"))

That should fix it up :)


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=539482

IF Statements and Time
 

Hi!

Try this:

=IF(COUNT(C3:D3)=2,IF(--G3TIME(0,7,0),"No",IF(--G3<=TIME(0,7,0),"Yes")),"")

Biff

wrote in message
oups.com...
Hello all,

I am having a bit of trouble with trying to use the IF Statement in
Excel when it comes to TIME.

This is what I am trying to do:

In Cell G3, it calculates minutes elapsed by using the following:

=TEXT(D3-C3, "h:mm:ss")

In Cell H3, I am trying to make it so that if Cell G3 is more than 7
Minutes, it writes "NO", and if the same cell is Less or Equal to 7, it
writes "YES"

Here is what I have in Cell H3:

=IF('7 or Less Articles'!G37,"No",IF('7 or Less
Articles'!G3<=7,"Yes"))

To make a long story short, it's not calculating properly.

Cell D3 is 9:03
Cell D3 is 9:00

9:03 minus 9:00 is 3 minutes, therefore Cell H3 should say "YES" -
instead it's saying "NO" I've been at this for several days trying to
find a solution.

I will appreciative of any help someone can provide.

IF Statements and Time
 

You can probably even go with this:

=IF(COUNT(C3:D3)=2,IF(--G3TIME(0,7,0),"No","Yes"),"")

If G3 is not "7" then it has to be <="7" *unless* D3 < C3 in which case the
formula in G3 will return an error and this will propagate to H3.

Biff

"Biff" wrote in message
...
Hi!

Try this:

=IF(COUNT(C3:D3)=2,IF(--G3TIME(0,7,0),"No",IF(--G3<=TIME(0,7,0),"Yes")),"")

Biff

wrote in message
oups.com...
Hello all,

I am having a bit of trouble with trying to use the IF Statement in
Excel when it comes to TIME.

This is what I am trying to do:

In Cell G3, it calculates minutes elapsed by using the following:

=TEXT(D3-C3, "h:mm:ss")

In Cell H3, I am trying to make it so that if Cell G3 is more than 7
Minutes, it writes "NO", and if the same cell is Less or Equal to 7, it
writes "YES"

Here is what I have in Cell H3:

=IF('7 or Less Articles'!G37,"No",IF('7 or Less
Articles'!G3<=7,"Yes"))

To make a long story short, it's not calculating properly.

Cell D3 is 9:03
Cell D3 is 9:00

9:03 minus 9:00 is 3 minutes, therefore Cell H3 should say "YES" -
instead it's saying "NO" I've been at this for several days trying to
find a solution.

I will appreciative of any help someone can provide.