IF Statements and Time

[roland.aspiras]

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'!G3>7,"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.

 

[systemx]

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]

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

 

[Biff]

Hi!

Try this:

=IF(COUNT(C33)=2,IF(--G3>TIME(0,7,0),"No",IF(--G3<=TIME(0,7,0),"Yes")),"")

Biff

 

[Biff]

You can probably even go with this:

=IF(COUNT(C33)=2,IF(--G3>TIME(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