i need to achieve a value >28/08/2009

to achieve this i have a field where the user enters the date they wish to
filter on

this is translated to the filter criteria by the following formula

= ">" & B3

the problem is i get a result of >40053

if i put "" around the date i get >"28/08/2009"

this does not work with the advanced filter.

help

what formula do i need to achieve my goal

Try something like this:

=A8>B$3

Where A8 is the first cell of the range to filter.

--
Biff
Microsoft Excel MVP


"Joseph Atie" <(E-Mail Removed)> wrote in message
news:2F3FAA76-C4E9-4683-8DC8-(E-Mail Removed)...
>i need to achieve a value >28/08/2009
>
> to achieve this i have a field where the user enters the date they wish to
> filter on
>
> this is translated to the filter criteria by the following formula
>
> = ">" & B3
>
> the problem is i get a result of >40053
>
> if i put "" around the date i get >"28/08/2009"
>
> this does not work with the advanced filter.
>
> help
>
> what formula do i need to achieve my goal

Are you saying i should make that the critera for the filter?

i tried this

Data!$E$5>Data!$E$2 as the critera

as well as

Data!$F$2>Interface!$B$3 (its across sheets)

all i get from the advanced filter screen is reference is not valid

can you give me more info

"T. Valko" wrote:

> Try something like this:
>
> =A8>B$3
>
> Where A8 is the first cell of the range to filter.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Joseph Atie" <(E-Mail Removed)> wrote in message
> news:2F3FAA76-C4E9-4683-8DC8-(E-Mail Removed)...
> >i need to achieve a value >28/08/2009
> >
> > to achieve this i have a field where the user enters the date they wish to
> > filter on
> >
> > this is translated to the filter criteria by the following formula
> >
> > = ">" & B3
> >
> > the problem is i get a result of >40053
> >
> > if i put "" around the date i get >"28/08/2009"
> >
> > this does not work with the advanced filter.
> >
> > help
> >
> > what formula do i need to achieve my goal
>
>
>

Hi,

Use =">"&TEXT(B3,"d/mm/yyyy")

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Joseph Atie" wrote:

> i need to achieve a value >28/08/2009
>
> to achieve this i have a field where the user enters the date they wish to
> filter on
>
> this is translated to the filter criteria by the following formula
>
> = ">" & B3
>
> the problem is i get a result of >40053
>
> if i put "" around the date i get >"28/08/2009"
>
> this does not work with the advanced filter.
>
> help
>
> what formula do i need to achieve my goal

Try this to see how it works....

A7 = header = Date
A8 = 8/25/2009
A9 = 8/26/2009
A10 = 8/27/2009
A11 = 8/28/2009
A12 = 8/29/2009
A13 = 8/30/2009

User enters a date in cell B3.

B3 = user entered date = 8/28/2009 (I use m/d/y format)

Enter this formula in C3:

=A8>B$3

Make the reference to the first cell of the range to filter a relative
reference.

Select the range A7:A13
Goto the menu Data>Filter>Advanced Filter
The List Range will already be entered.
As the Criteria Range use: C$2:C$3

Note that we use C2 and C3. Leave C2 as an empty cell.

Click OK


--
Biff
Microsoft Excel MVP


"Joseph Atie" <(E-Mail Removed)> wrote in message
news:032D9C38-F1CB-477E-8A5D-(E-Mail Removed)...
> Are you saying i should make that the critera for the filter?
>
> i tried this
>
> Data!$E$5>Data!$E$2 as the critera
>
> as well as
>
> Data!$F$2>Interface!$B$3 (its across sheets)
>
> all i get from the advanced filter screen is reference is not valid
>
> can you give me more info
>
> "T. Valko" wrote:
>
>> Try something like this:
>>
>> =A8>B$3
>>
>> Where A8 is the first cell of the range to filter.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Joseph Atie" <(E-Mail Removed)> wrote in message
>> news:2F3FAA76-C4E9-4683-8DC8-(E-Mail Removed)...
>> >i need to achieve a value >28/08/2009
>> >
>> > to achieve this i have a field where the user enters the date they wish
>> > to
>> > filter on
>> >
>> > this is translated to the filter criteria by the following formula
>> >
>> > = ">" & B3
>> >
>> > the problem is i get a result of >40053
>> >
>> > if i put "" around the date i get >"28/08/2009"
>> >
>> > this does not work with the advanced filter.
>> >
>> > help
>> >
>> > what formula do i need to achieve my goal
>>
>>
>>