Board index » Excel » Greater than/Less than a certain Time

Excel231
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


-

Try:



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



Regards,

Steve



Phrank wrote:



-

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" wrote:

Hi Steve,



Unfortunately, that still gives me 'no' even when the time is less

that 3:30 PM.



Frank



On 23 Nov 2006 07:18:32 -0800, wisccal@googlemail.com wrote:

Try this:



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





"Phrank" wrote:

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" <pbeal@hotmail.com>wrote in message



-

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

everyone!



Frank



On Thu, 23 Nov 2006 08:31:01 -0800, Teethless mama

<Teethlessmama@discussions.microsoft.com>wrote: