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

Thread: Time Between?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way to use a formula like :

    If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

    to determine if a given cell occurs between the two listed times? Thanks!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 14:16, StrangeLuck wrote:
    Is there a way to use a formula like :

    If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

    to determine if a given cell occurs between the two listed times? Thanks!
    formula wise it simply be if(a1<.25

    for a cell formatted as time.

    if it is time and date it would be
    if(MOD(A1,1)<.25,...

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 14:16, StrangeLuck wrote:
    Is there a way to use a formula like :

    If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

    to determine if a given cell occurs between the two listed times? Thanks!
    Hi StrangeLuck:
    I don't know the context in which you are using this formula, but syntax-wise the following will work

    =IF(AND(I5>"12:00 AM"+0,I5<"6:00 AM"+0),"YES","NO")




    _________________
    Yogi Anand

    Edit: Deleted inactive website from hardcoded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 14:07 ]

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

    Default



    What do you mean by 12:00 AM?

    If you want to test if time is between 0:00
    and 6:00. Put the criteria in say B1 and B2
    and use

    =AND(I5>B1,I5

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave:
    Logic wise, I agree with you. However in the original post, StrangeLuck had asked if he could directly use 12:00 AM and 6:00 AM in an IF formula, that is why response to him that I don't know in what context he asked the question, but syntax-wise 12:00 AM and 6:00 AM could be directly used in the formula.

    Regards!

    Yogi Anand

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

    Default


    If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

    How can a value be both greater than 12:00
    and less than =6:00?


  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 16:43, Dave Patton wrote:

    If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

    How can a value be both greater than 12:00
    and less than =6:00?

    Dave: It does appear to be strange, however in my formula

    ="12:00 AM"+0 is coerced into 0.00
    and
    ="6:00 AM"+0 is coerced into 0.25

    Regards!
    Yogi Anand

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I should have been clearer in my question, I suppose. I am looking for a VBA method whereby I could determine if a value in a certain cell, say B1, is between 12:00 AM and 6:00 AM. Thanks!

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

    Default



    Do not use 12:00 AM

    consider

    with cell references =AND(I5>B1,I5 or
    =AND(I5>"6:00"+0,I5<"12:00"+0)

    or =AND(I5>0.25,I5<0.5)

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-23 07:51, Dave Patton wrote:


    Do not use 12:00 AM

    consider

    with cell references =AND(I5>B1,I5 or
    =AND(I5>"6:00"+0,I5<"12:00"+0)

    or =AND(I5>0.25,I5<0.5)
    But, I wanted it to be a VBA solution, including the AM.

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
  •