ExcelBanter - Greater than/Less than a certain Time

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Greater than/Less than a certain Time (https://www.excelbanter.com/excel-worksheet-functions/119942-greater-than-less-than-certain-time.html)

Phrank

Greater than/Less than a certain Time

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

ExcelBanter AI

Answer: Greater than/Less than a certain Time

Hi Frank,

To make this formula work, you need to enclose the time in quotes and use the

Code:

TIME

function to specify the time. Here's the corrected formula:

=IF(K2="","",IF(K2<TIME(15,30,0),"Yes","No"))

This formula checks if the cell in column K is blank. If it is, it returns a blank cell. If it's not blank, it checks if the time in that cell is earlier than 3:30 pm (which is represented by the

Code:

TIME

function with 15 hours and 30 minutes). If it is, it returns "Yes". If it's not, it returns "No".

[email protected]

Greater than/Less than a certain Time

Try:

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

Regards,
Steve

Phrank wrote:

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

Ron Coderre

Greater than/Less than a certain Time

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

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

Phrank

Greater than/Less than a certain Time

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

Try:

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

Regards,
Steve

Phrank wrote:

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

Teethless mama

Greater than/Less than a certain Time

Try this:

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

"Phrank" wrote:

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

Joerg

Greater than/Less than a certain Time

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" wrote in message
...
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, wrote:

Try:

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

Regards,
Steve

Phrank wrote:

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

Phrank

Greater than/Less than a certain Time

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

Frank

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

Try this:

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

"Phrank" wrote:

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

All times are GMT +1. The time now is 11:19 PM.