Greater than/Less than a certain Time

[Phrank]

Hello,

I've got a helper column (column B) with a formula that looks at a
time stamp in column A. I'm trying to get each cell in column B to
say Yes if the time in the adjoining cell in column A is earlier than
3:30 pm, and No if it's later than 3:30 pm. What I currently have is
below, but this returns the error message #NAME?

=IF(K2="","",IF(K2<3:30 PM,"yes","no"))

I also tried putting the desired cutoff time in quotes (as below), but
that returns 'no' no matter what time is in the cell.

=IF(K2="","",IF(K2<"3:30 PM","yes","no"))

What do I need to change to make this work? Thanks.

Frank

 

[wisccal]

Try:

=IF(K2="","",IF(K2<Timevalue("3:30 PM"),"yes","no"))

Regards,
Steve

 

[Ron Coderre]

What do I need to change to make this work?

A little bit of number type coercion:
=IF(K2="","",IF(K2<(--"3:30 PM"),"yes","no"))

The dbl-negative causes Excel to implicitly convert the text "3:30 PM" into
a time value.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Phrank]

Hi Steve,

Unfortunately, that still gives me 'no' even when the time is less
that 3:30 PM.

Frank

 

[Teethless mama]

Try this:

=IF(K2="","",IF(K2<--"3:30 PM","yes","no"))

 

[Joerg]

Formula is OK. Gives 'yes' for times less than 3:30 PM.
Please check if your date cell (K2) is correctly formatted as 'time'.

Joerg

 

[Phrank]

Excellent! All works now. Formatting was wrong. Thank you very much
everyone!

Frank