Convert cell reference to text string

Hi,

I have one cell that the contents is a date that you shall be to change. This date is put in the format of 2007-02-06 (cell N8).

In order to get the advanced filter to work, I need the cell that shall be used as criteria to contain a text string like the following:

=">=2006-06-01"

and to get this, I have found that I can use the formula

Please Login or Register  to view this content.

But then the advanced filter will not work. Do I need to somehow convert this formula to a static text string to use when filtering? If yes, how do I do that?

Hmm, I hope everyone understands my description :D

When selecting "Format Cells" and "Date", that format is there for me to choose.

Nevermind the format, it works for me and I can choose it. But that ain't the problem.

What can be done to solve the problem?

hello packe

You shouldn’t need to convert anything to text. You can use a formula for this in the criteria range.

Criteria label should be blank.

Put this formula in the criteria range

=A5>=$N$8

where A5 is the first record in your advanced filter list range and N8 contains a date. Note: the cell containing the formula will return TRUE or FALSE but that won’t affect how the filtering works

Hi,

Just to see if I have understood it correctly, the formula =A5>=$N$8 should be written like =por_omps_d!E1>=$N8$9 in my sheet according to the following:

Sample data from the sheet containing all the data:
REQ ID ORDER_USER REQ_SEV REQ_STATE REG_DATE
OMPS00000001 ETXALRO N C 2004-06-03 18:50
OMPS00000002 ETXALRO C C 2004-08-16 10:43
OMPS00000003 ETXTLIN N C 2004-08-18 09:02

REG_DATE is position E1 and is what I want to sort on. The sheet this data is on is called por_omps_d.

The sheet where the criterias is written is called Analys.

Have I understood it correctly?

The date format used is YYYY-MM-DD HH:MM in the data sheet and "criteria cell" (where I put the range to sort on).

Hmm, must be missing something. Not getting it working.

Will =por_omps_d!E2>=$N$8 only sort the cell E2 or will it be all in column E (starting from cell E2)?

The format of the dates shouldn't matter as long as they are actually dates, not text. If you can use Cells > Format to reformat dates then they must be dates.

I attach an example based on your data.

I made the list range $A$1:$E$20 (in por_omps_d worksheet) and defined the criteria range as analys!$A$1:$A$2 where A1 is the blank criteria label and A2 contains the formula =por_omps_d!E2>=$N$8

Great, thanks! That solved it =)

If I now also want a upper limit so that I i.e. can sort out only those between 2007-01-01 and 2007-02-01, how would I modify this to get that working? Just add another in the criteria range?

Yes, you could put your second date in N9 and use this formula instead

=AND(por_omps_d!E2>=$N$8,por_omps_d!E2<=$N$9)

Thanks, but I don't get it limited to the uppder criteria. All rows above the lower criteria is selected.