Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: IF function in Time Range

  1. #1
    New Member
    Join Date
    Mar 2009
    Location
    Auckland - NZ
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool IF function in Time Range

    Good day... I need an IF Function that will allow me to action a time in a time range:

    ... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...
    ... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty

    Any help is appreciated.

  2. #2
    New Member
    Join Date
    Mar 2009
    Location
    New Delhi, India
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function in Time Range

    Think this might help if you have the start time of the range in cell A10 and end time of the range in cell B10:

    C10 is the time to check.

    Code:
    =IF(AND(C10>=A10,C10<=B10),1,"")

  3. #3
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,457
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function in Time Range

    welcome to MrExcel Board.....

    try this

    =IF(AND(C20>=TIME(4,0,0),C20<=TIME(4,29,0)),1,"")
    where c20 is the time to check

  4. #4
    New Member
    Join Date
    Mar 2009
    Location
    Auckland - NZ
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: IF function in Time Range

    Great... It works. Thanks for your help.

  5. #5
    New Member
    Join Date
    Mar 2009
    Location
    Auckland - NZ
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: IF function in Time Range

    I have still a problem with a "time" field (in our expl C20)...
    C20 is an answer to a Fuction, and I think, that the IF Function is not able to analize an answer and use it as a data for another function. Could that be right?

  6. #6
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,457
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function in Time Range

    no excel uses the answer. Post a sample of your data with the formula you are using

  7. #7
    New Member
    Join Date
    Mar 2009
    Location
    Auckland - NZ
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: IF function in Time Range

    1
    ABCDEFGHIJK
    2Dep LTArr LTRel ETD minusRel Time LTZ Cor +Rel Time ZRel Time AKL03:30-03:5904:00-04:2904:30-04:5905:00-05:29
    3
    4
    5
    6
    7
    806:4511:3001:3005:1511:0016:1504:15
    906:4009:5001:3005:1011:0016:1004:10
    1019:0022:1001:3017:3011:0004:3016:30
    1103:1506:2501:3001:4511:0012:4500:45
    1212:2515:3501:3010:5511:0021:5509:55
    1321:3500:4501:3020:0511:0007:0519:05


    Fiel G8 uses: =G8+("12:00">G8)-"12:00" to change F8 to local time.

    Column G should be analyzed according my idea as before, and the answer with a 1 should end up in the H to K - 8 to 13.

    Thanks

  8. #8
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,457
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function in Time Range

    is this it?
    Sheet4

     ABCDEFGHIJK
    1Dep LTArr LTRel ETD minusRel Time LTZ Cor +Rel Time ZRel Time AKL03:30-03:5904:00-04:2904:30-04:5905:00-05:29
    2    
    33:304:004:305:00
    43:594:294:595:29
    5    
    6           
    76:4511:301:305:1511:0016:154:15 1  
    86:409:501:305:1011:0016:104:10 1  
    919:0022:101:3017:3011:004:3016:30    
    103:156:251:301:4511:0012:450:45    
    1112:2515:351:3010:5511:0021:559:55    
    1221:350:451:3020:0511:007:0519:05    

    Spreadsheet Formulas
    CellFormula
    H7=IF(AND($G7>=H$3,+$G7<H$4),1,"")
    I7=IF(AND($G7>=I$3,+$G7<I$4),1,"")
    J7=IF(AND($G7>=J$3,+$G7<J$4),1,"")
    K7=IF(AND($G7>=K$3,+$G7<K$4),1,"")
    H8=IF(AND($G8>=H$3,+$G8<H$4),1,"")
    I8=IF(AND($G8>=I$3,+$G8<I$4),1,"")
    J8=IF(AND($G8>=J$3,+$G8<J$4),1,"")
    K8=IF(AND($G8>=K$3,+$G8<K$4),1,"")
    H9=IF(AND($G9>=H$3,+$G9<H$4),1,"")
    I9=IF(AND($G9>=I$3,+$G9<I$4),1,"")
    J9=IF(AND($G9>=J$3,+$G9<J$4),1,"")
    K9=IF(AND($G9>=K$3,+$G9<K$4),1,"")
    H10=IF(AND($G10>=H$3,+$G10<H$4),1,"")
    I10=IF(AND($G10>=I$3,+$G10<I$4),1,"")
    J10=IF(AND($G10>=J$3,+$G10<J$4),1,"")
    K10=IF(AND($G10>=K$3,+$G10<K$4),1,"")
    H11=IF(AND($G11>=H$3,+$G11<H$4),1,"")
    I11=IF(AND($G11>=I$3,+$G11<I$4),1,"")
    J11=IF(AND($G11>=J$3,+$G11<J$4),1,"")
    K11=IF(AND($G11>=K$3,+$G11<K$4),1,"")
    H12=IF(AND($G12>=H$3,+$G12<H$4),1,"")
    I12=IF(AND($G12>=I$3,+$G12<I$4),1,"")
    J12=IF(AND($G12>=J$3,+$G12<J$4),1,"")
    K12=IF(AND($G12>=K$3,+$G12<K$4),1,"")


    Excel tables to the web >> Excel Jeanie HTML 4

  9. #9
    New Member
    Join Date
    Mar 2009
    Location
    Auckland - NZ
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: IF function in Time Range

    Something is not right...
    It works on a test sheet but not with the answer from the field G7.
    Is my function correct in the G7 to add 12h to the field F7: =F7+("12:00">F7)-"12:00"?
    Thanks

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
  •