Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: IF function for time

  1. #1
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie,

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

    Brian

  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha,

    Make that 30 mins and aircraft type 475

    Brian

  7. #7
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •