advanced filter and format

[CG Rosén]

Good day Group,

Have a problem concering advanced filter and formats. In a column there is
data
representing dates. The data are put in the column by textboxes, the column
is formated
as text.
Date
20060201
20060225
20050303
20040815
20060525
etc

When trying to filter by the advanced filter there is nothing found by using
criteria like:
Date
<=20050101
but is working when the criteria looks like:
Date
<=20050101*
have checked the length of the textstring and there are no "hidden" letters
or spaces.
It also looks like <= operand is working fine but when using the >= the data
that is =
to the criteria is ignored.

Hopeful for some hints how to go on with this problem

Brgds

CG Rosén

[Peter T]

I don't really follow what you are doing and what you are comparing,
strings, numbers or date values (eg today's date is 38882). Couple of
comments in case relevant -

Just because you've formated cells as text doesn't necessarily mean they
contain text, confirm with say =istext(a1)

A number that is text will evaluate to greater than any number or
date-value, eg
="1" > 2 ' true

If you want to compare a number as text with a number try
="1" > TEXT(2,"@") 'false

or
=VALUE("1") > 2 ' false

or if only numbers in the string
="1"*1>2 ' false

Regards,
Peter T


"CG Rosén" <[email protected]> wrote in message
news:[email protected]...
>
> Good day Group,
>
> Have a problem concering advanced filter and formats. In a column there is
> data
> representing dates. The data are put in the column by textboxes, the
column
> is formated
> as text.
> Date
> 20060201
> 20060225
> 20050303
> 20040815
> 20060525
> etc
>
> When trying to filter by the advanced filter there is nothing found by
using
> criteria like:
> Date
> <=20050101
> but is working when the criteria looks like:
> Date
> <=20050101*
> have checked the length of the textstring and there are no "hidden"
letters
> or spaces.
> It also looks like <= operand is working fine but when using the >= the
data
> that is =
> to the criteria is ignored.
>
> Hopeful for some hints how to go on with this problem
>
> Brgds
>
> CG Rosén
>
>
>
>
>
>
>

[Peter T]

I don't really follow what you are doing and what you are comparing,
strings, numbers or date values (eg today's date is 38882). Couple of
comments in case relevant -

Just because you've formated cells as text doesn't necessarily mean they
contain text, confirm with say =istext(a1)

A number that is text will evaluate to greater than any number or
date-value, eg
="1" > 2 ' true

If you want to compare a number as text with a number try
="1" > TEXT(2,"@") 'false

or
=VALUE("1") > 2 ' false

or if only numbers in the string
="1"*1>2 ' false

Regards,
Peter T


"CG Rosén" <[email protected]> wrote in message
news:[email protected]...
>
> Good day Group,
>
> Have a problem concering advanced filter and formats. In a column there is
> data
> representing dates. The data are put in the column by textboxes, the
column
> is formated
> as text.
> Date
> 20060201
> 20060225
> 20050303
> 20040815
> 20060525
> etc
>
> When trying to filter by the advanced filter there is nothing found by
using
> criteria like:
> Date
> <=20050101
> but is working when the criteria looks like:
> Date
> <=20050101*
> have checked the length of the textstring and there are no "hidden"
letters
> or spaces.
> It also looks like <= operand is working fine but when using the >= the
data
> that is =
> to the criteria is ignored.
>
> Hopeful for some hints how to go on with this problem
>
> Brgds
>
> CG Rosén
>
>
>
>
>
>
>