# Formula for a range between Dates and Times

1. ## 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. ## 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. ## 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. ## 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")

##### Users Browsing this Thread

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

#### 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