+ Reply to Thread
Results 1 to 7 of 7

Formula check if in between time range from 2 data

  1. 06-08-2018, 05:47 AM #1
    BuzyxBuddy
    BuzyxBuddy is offline
    Registered User
    Join Date
    05-11-2016
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    11

    Unhappy Formula check if in between time range from 2 data

    Hi All,

    I was stucked in checking the time range true or false here..

    A1: 10:00 PM
    B1: 06:00 AM

    how to tell if C1: 05:00 AM is between the range as TRUE?

    I've tried the formula below it doesn't work..

    IF(AND(C1>A1,C16<B1),"YES","NO"))
    Reply With QuoteRegister To Reply
  2. 06-08-2018, 06:30 AM #2
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Formula check if in between time range from 2 data

    =IF(MOD(MEDIAN(A1,B1+(B1<=A1),C1+(B1<=A1)*(C1<=B1)),1)=C1,"Yes","No")
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  3. 06-08-2018, 07:36 AM #3
    BuzyxBuddy
    BuzyxBuddy is offline
    Registered User
    Join Date
    05-11-2016
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    11

    Re: Formula check if in between time range from 2 data

    Quote Originally Posted by nflsales View Post
    =IF(MOD(MEDIAN(A1,B1+(B1<=A1),C1+(B1<=A1)*(C1<=B1)),1)=C1,"Yes","No")
    Hi nflsales,

    Thanks it's almost there but 10:00 PM still recognize as No, any idea?
    10:00 PM 6:00 AM 01:00 AM Yes
    02:00 AM Yes
    03:00 AM Yes
    04:00 AM Yes
    05:00 AM Yes
    06:00 AM Yes
    07:00 AM No
    08:00 AM No
    09:00 AM No
    10:00 AM No
    11:00 AM No
    12:00 PM No
    01:00 PM No
    02:00 PM No
    03:00 PM No
    04:00 PM No
    05:00 PM No
    06:00 PM No
    07:00 PM No
    08:00 PM No
    09:00 PM No
    10:00 PM No
    11:00 PM Yes
    12:00 AM Yes
    Reply With QuoteRegister To Reply
  4. 06-08-2018, 08:32 AM #4
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Formula check if in between time range from 2 data

    I think when you enter times manually, this problem will nor comes
    when you add times with something then it will happen due to fractions.
    Reply With QuoteRegister To Reply
  5. 06-08-2018, 08:34 AM #5
    xfile
    xfile is offline
    Registered User
    Join Date
    06-07-2018
    Location
    Arusha
    MS-Off Ver
    2017
    Posts
    4

    Red face Re: Formula check if in between time range from 2 data

    Hello Buzy, I am new here trying to help, I came up with this formula

    =IF((A1-C1)*(B1-C1)>=0,"Yes","No")

    I could not be able to attach a file, but those will work. Thank you, if you are satisfied please give me a star to encourage me to help more . I started yesterday in this forum.

    10:00 PM 6:00 AM 1:00 AM Yes
    10:00 PM 6:00 AM 2:00 AM Yes
    10:00 PM 6:00 AM 3:00 AM Yes
    10:00 PM 6:00 AM 4:00 AM Yes
    10:00 PM 6:00 AM 5:00 AM Yes
    10:00 PM 6:00 AM 6:00 AM Yes
    10:00 PM 6:00 AM 7:00 AM No
    10:00 PM 6:00 AM 8:00 AM No
    10:00 PM 6:00 AM 9:00 AM No
    10:00 PM 6:00 AM 10:00 AM No
    10:00 PM 6:00 AM 11:00 AM No
    10:00 PM 6:00 AM 12:00 PM No
    10:00 PM 6:00 AM 1:00 PM No
    10:00 PM 6:00 AM 2:00 PM No
    10:00 PM 6:00 AM 3:00 PM No
    10:00 PM 6:00 AM 4:00 PM No
    10:00 PM 6:00 AM 5:00 PM No
    10:00 PM 6:00 AM 6:00 PM No
    10:00 PM 6:00 AM 7:00 PM No
    10:00 PM 6:00 AM 8:00 PM No
    10:00 PM 6:00 AM 9:00 PM No
    10:00 PM 6:00 AM 10:00 PM Yes
    10:00 PM 6:00 AM 11:00 PM Yes
    10:00 PM 6:00 AM 12:00 AM Yes
    Reply With QuoteRegister To Reply
  6. 06-08-2018, 08:40 AM #6
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Formula check if in between time range from 2 data

    Quote Originally Posted by xfile View Post
    Hello Buzy, I am new here trying to help, I came up with this formula

    =IF((A1-C1)*(B1-C1)>=0,"Yes","No")

    I could not be able to attach a file, but those will work. Thank you, if you are satisfied please give me a star to encourage me to help more . I started yesterday in this forum.

    10:00 PM 6:00 AM 1:00 AM Yes
    10:00 PM 6:00 AM 2:00 AM Yes
    10:00 PM 6:00 AM 3:00 AM Yes
    10:00 PM 6:00 AM 4:00 AM Yes
    10:00 PM 6:00 AM 5:00 AM Yes
    10:00 PM 6:00 AM 6:00 AM Yes
    10:00 PM 6:00 AM 7:00 AM No
    10:00 PM 6:00 AM 8:00 AM No
    10:00 PM 6:00 AM 9:00 AM No
    10:00 PM 6:00 AM 10:00 AM No
    10:00 PM 6:00 AM 11:00 AM No
    10:00 PM 6:00 AM 12:00 PM No
    10:00 PM 6:00 AM 1:00 PM No
    10:00 PM 6:00 AM 2:00 PM No
    10:00 PM 6:00 AM 3:00 PM No
    10:00 PM 6:00 AM 4:00 PM No
    10:00 PM 6:00 AM 5:00 PM No
    10:00 PM 6:00 AM 6:00 PM No
    10:00 PM 6:00 AM 7:00 PM No
    10:00 PM 6:00 AM 8:00 PM No
    10:00 PM 6:00 AM 9:00 PM No
    10:00 PM 6:00 AM 10:00 PM Yes
    10:00 PM 6:00 AM 11:00 PM Yes
    10:00 PM 6:00 AM 12:00 AM Yes
    Change your time as 06:00 , 12:00, 7:00
    and check your formula
    Reply With QuoteRegister To Reply
  7. 06-14-2018, 07:48 AM #7
    BuzyxBuddy
    BuzyxBuddy is offline
    Registered User
    Join Date
    05-11-2016
    Location
    Malaysia
    MS-Off Ver
    2007
    Posts
    11

    Re: Formula check if in between time range from 2 data

    yea nflsales is right i think it's the fraction problem cause i'm still facing the same issue.
    i guess i will have to key in a standard list and using vlookup to detect it then.
    Thanks anyway guys!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to check if a time noted is within designated window or range in Excel
    By nk_aspire in forum Excel General
    Replies: 5
    Last Post: 02-12-2018, 05:43 AM
  2. Check if event occurs within time range
    By excellinginlife in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-20-2016, 10:43 AM
  3. [SOLVED] Check Date and Time in range and show Msgbox
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2016, 09:08 AM
  4. [SOLVED] if formula to check a criteria in a range of data within a column
    By rajeshmarndi in forum Excel General
    Replies: 2
    Last Post: 07-10-2016, 09:47 AM
  5. [SOLVED] Formula that check for duplicate data in a column range
    By eugh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-12-2012, 02:57 AM
  6. Check to see if a time is within a specified range
    By MrsA in forum Excel General
    Replies: 4
    Last Post: 08-04-2011, 03:54 PM
  7. Check value in a time range
    By badin in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-25-2008, 06:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1