+ Reply to Thread
Results 1 to 4 of 4

Formula for a range between Dates and Times

  1. #1
    Registered User
    Join Date
    07-18-2012
    Location
    Cambridge
    MS-Off Ver
    Excel 2007
    Posts
    2

    Formula for a range between Dates and Times

    Hello,

    I have a bit of a problem. I am trying to create a formula that can determine if a date, appearing as DD/MM/YYYY 12:00 PM falls within a certain time frame. Is there a way I could check it to see if falls between a certain time on that date, (example, any time between 5 in the morning and 3 in the afternoon)? I imagine it's probably just checking the hour and minutes within a certain range, but I'm not sure how to do that.

    Example

    I'd want to see if the times for the below were between 5:00 AM and 4:00 PM (regardless of the date) and if yes, then have a yes result, otherwise no.

    20/10/2009 11:03 AM


    Any advice would be appreciated. Hope this is clear enough.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula for a range between Dates and Times

    Hi,

    =IF(AND(A1-INT(A1)>=5/24,A1-INT(A1)<=16/24),"Yes","No")

  3. #3
    Registered User
    Join Date
    07-18-2012
    Location
    Cambridge
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Formula for a range between Dates and Times

    Amazing. Thanks!

    One more quick thing, is there any way to set minutes as well, so to get to be 5:30 or so?

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula for a range between Dates and Times

    Hi

    Yes.

    Time is the decimal part of the date/time number. There are 1440 minutes in the day, so 5:30 is 330 minutes and hence the decimal is 0.229167. Therefore the formula would be

    =IF(AND(A1-INT(A1)>=330/1440,A1-INT(A1)<=16/24),"Yes","No")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1