Find a date less or greater than a particular date from a range of cells

**laser2302** · 12-12-2011, 03:47 AM

Hi Everyone,

Can anyone help me find a way to search and display a date from a range of cells based on less than or greater than criteria. For example I have following dates in column A:

A1: 2011/01/04
A2: 2011/02/01
A3: 2011/03/01
A4: 2011/04/01

I want to search for the date which is less than 2011/02/01 from the A column and display that date in B1 cell for example. How do I do that?

Thanks!

**Fotis1991** · 12-12-2011, 03:59 AM

Hi laser2302.

A1: 2011/01/04
A2: 2011/02/01
A3: 2011/03/01
A4: 2011/04/01

The simplest way to do this, is:

In d1(per example) put the date 2011/02/01

Now in B1, put the formula:

=A1:A4<D1

Drag down to A4

Hope to helps you.

**laser2302** · 12-12-2011, 04:05 AM

Originally Posted by Fotis1991

Hi laser2302.

The simplest way to do this, is:

In d1(per example) put the date 2011/02/01

Now in B1, put the formula:

=A1:A4<D1

Drag down to A4

Hope to helps you.

I tried that even before posting this thread, but it always gives me an error message "#VALUE"

**Fotis1991** · 12-12-2011, 04:14 AM

You do something wrong!

Take a look to the attachement.

Hope to helps you

**laser2302** · 12-12-2011, 04:36 AM

I think maybe my question is not clear enough. What I want is if I enter a date, excel should return me the date which is less than the entered date and yet closest to it. For example if I enter 2011/02/01 than excel should return 2011/01/04, if I enter 2011/04/01 then it should return 2011/03/01 and so on.