Greater than/Less than a certain Time (revisited)

[Phrank]

Hello,

I posted a question a couple days ago, and although it seemed that
things were working ok, they weren't. I've got a date/time stamp IN
in column A, and a date/time stamp OUT in column B. I need column C
to say Yes if the time OUT is less than 3:30 PM, and NO if the time
out is after 3:30 PM. The format in both the IN and the OUT columns
is 'mm/dd/yy h:mm AM/PM' (e.g., 11/24/06 9:39 AM). Below are the two
formulas that I have tried:

=IF(J2="","",IF(J2<="3:30 PM","Yes","No"))
=IF(J2="","",IF(J2<TIMEVALUE("3:30 PM"),"yes","no"))

What adjustments do I need to make to this? Thanks for any help.

Frank

 

[T. Valko]

Try this:

=IF(J2="","",IF(MOD(J2,1)<TIME(15,30,0),"Yes","No"))

Biff

 

[Phrank]

Beautiful! Works great. Thanks Biff!!!

Frank

Try this:

=IF(J2="","",IF(MOD(J2,1)<TIME(15,30,0),"Yes","No"))

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Beautiful! Works great. Thanks Biff!!!

Frank

 

[Phrank]

Hi again,

As I said, this works great. However, it does focus only on the
time, regardless of the day. What would I need to add to also have it
look at the day? For example, if someone turned something IN on one
day, and it wasn't returned (OUT) until the next day, I need it to
return 'No' also. As it is right now, as long as it is before 3:30,
it doesn't matter if the OUT date is a month after the item was turned
IN. Thanks for any additional help.

Frank

 

[Phrank]

Hi again,

I found a way to do this. Instead of having one column for the IN and
the OUT with a date/time stamp, I split each of these into two
columns, one with date and the other with time. Then, in the column
that asks if the item was returned (OUT) before 3:30, I put this
formula:

=IF(L7="","",IF(AND(DATEDIF(A7,B7,"D")=0,MOD(C7,1)<TIME(15,30,0)),"Yes","No"))

It works great. Thanks again for looking and with your help.

Frank