IF function for time

[Brian from Maui]

Aloha,

I have in col D 7:00 E 7:45 F 7:15 G 7:46
Am using this formula to count occurences

IF(F7<>"",IF(G7<>"",IF(AND(ROUND((G7-F7),5)>ROUND((E7-D7),5),G7>E7),0,1),""),"")

How can I add another IF statement to ask IF 475, then 45 mins, occurrence should be 0

Brian

 

[Barrie Davidson]

On 2002-03-25 14:48, Brian from Maui wrote:
Barrie,

It works.
But...I know you all hate this...
instead of 475 I'd like to refer this to say
717 30 mins, D10 45 mins, 767 60 mins

Brian

Okay, my example assumes the aircraft type is in cell B1 and you have a lookup table in B6:C8 that has the aircraft type in column B and the time limit in column C (the time limit is just a regular number, not recognized as time by Excel).

=IF(OR(F1="",G1=""),"",IF((G1-F1)*1440>VLOOKUP(B1,$B$6:$C$8,2,0),1,0))

Does this help?

 

[Aladin Akyurek]

On 2002-03-25 14:41, Barrie Davidson wrote:
Brian, will this work for you?

=IF(OR(F1="",G1=""),"",IF(AND(G1-F1>45/1440,T1<>475),1,0))


This will return 1 if the time is greater than 45 minutes AND the aircraft type (in cell T1) is not equal to 475.

Regards,

If what is desired is to obtain a count how many times 475 has failed to stay within 45 mins, then:

=(COUNT(F1:G1)=2)*(G1-F1>45/1440)*(T1=475)

Aladin

 

[Brian from Maui]

Barrie,

Yes it does, why didn't i think of a VLOOKUP.

Because........fill in the blank.

Mahalo Barrie and Dave.....

Aladin, I'll try yours too!

Brian
This message was edited by Brian from Maui on 2002-03-25 14:59