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

 

[Dave Patton]

I am not sure of your question.
Could you describe what you are trying to do?

=F3-E3>E7

With time in F3 and E3 and 0:45 in E7,
this gives True or False if interval in
greater than 45 minutes.

 

[Brian from Maui]

Aloha Dave,

Columns D and E are scheduled arrival and departure times
Columns F and G are the actual arrival and departures time.
If actual arr/dep times do not exceed 45 mins and aircraft type is 475, then occurrence is 0

Brian

 

[Barrie Davidson]

On 2002-03-25 14:33, Brian from Maui wrote:
Aloha Dave,

Columns D and E are scheduled arrival and departure times
Columns F and G are the actual arrival and departures time.
If actual arr/dep times do not exceed 45 mins and aircraft type is 475, then occurrence is 0

Brian

Aloha Brian, what cell do you specify the aircraft type?

Regards,

 

[Brian from Maui]

Barrie,

Right now, I'm setting this up, so how about T1

Brian

 

[Brian from Maui]

Aloha,

Make that 30 mins and aircraft type 475

Brian

 

[Barrie Davidson]

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,

 

[Barrie Davidson]

Okay, make that

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


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

 

[Brian from Maui]

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

 

[Dave Patton]

quick thought

=AND(G2-F2<T2,E2-D2<T2,B2=T1)*1

with T1=475, T2=0:45, b2 = plane (475)

The And gives True or False without the *1
You may not need the *1

With the new information, incorporate a lookup for the reference or use Sumproduct.

Put the planes etc into the Lookup or vlookup table.
This message was edited by Dave Patton on 2002-03-25 14:53
This message was edited by Dave Patton on 2002-03-25 14:54