IF function for time

Brian from Maui · Mar 25th, 2002, 03:12 PM

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 · Mar 25th, 2002, 03:27 PM

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 · Mar 25th, 2002, 03:33 PM

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 · Mar 25th, 2002, 03:35 PM

Quote:

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 · Mar 25th, 2002, 03:37 PM

Barrie,

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

Brian

Brian from Maui · Mar 25th, 2002, 03:39 PM

Aloha,

Make that 30 mins and aircraft type 475

Brian

Barrie Davidson · Mar 25th, 2002, 03:41 PM

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 · Mar 25th, 2002, 03:43 PM

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 · Mar 25th, 2002, 03:48 PM

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 · Mar 25th, 2002, 03:49 PM

quick thought

=AND(G2-F2
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 ]

Mar 25th, 2002, 03:41 PM	#7
Barrie Davidson MrExcel MVP Join Date: Feb 2002 Location: Winnipeg Posts: 2,330	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 "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Mar 25th, 2002, 03:43 PM	#8
Barrie Davidson MrExcel MVP Join Date: Feb 2002 Location: Winnipeg Posts: 2,330	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. __________________ Barrie Davidson "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 25th, 2002, 03:12 PM	#1
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 25th, 2002, 03:27 PM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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.

Mar 25th, 2002, 03:33 PM	#3
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 25th, 2002, 03:37 PM	#5
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Barrie, Right now, I'm setting this up, so how about T1 Brian

Mar 25th, 2002, 03:39 PM	#6
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Aloha, Make that 30 mins and aircraft type 475 Brian

Mar 25th, 2002, 03:48 PM	#9
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 25th, 2002, 03:49 PM	#10
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	quick thought =AND(G2-F2 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 ]