Formula for a range between Dates and Times

**Jim_Vengence** · 07-18-2012, 05:12 PM

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.

**Richard Buttrey** · 07-18-2012, 05:39 PM

Hi,

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

**Jim_Vengence** · 07-18-2012, 06:07 PM

Amazing. Thanks!

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

**Richard Buttrey** · 07-18-2012, 06:45 PM

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")