Filtering Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with 20 columns, each containing various types of data. Column A is Date Received. Column S is Date Returned. Am trying, unsuccessfully, by using advanced filter, to have the sheet display all records where date received is 7 days or more before "todays" date ONLY IF the Date Returned column does not contain a date. The display is in annother part of the sheet. How can I get the thing to do that? Any suggestions?
 
In the criteria area, leave the heading cell blank.
In the cell below, enter the following formula:

=AND(A2<=TODAY()-7,S2="")

where your first row of data is in row 2.

When you run the Advanced Filter, for the criteria area, select the
blank heading cell, and the cell with the formula
 
Hi

I would use a helper column and enter the following formula:
=IF(S1="",IF((TODAY()-A1)>=7,"Display",""),"")
copy this for all rows and filter with this column

HTH
Frank
 
I am assuming that the first date in A is in A3 thus the data starts in row
3 with the headers in 2
in let's say U2 put this formula

=AND(A3+7<=TODAY(),S3="")

leave U1 empty and put this in the criteria range

$U$1:$U$2

I am also assuming that the option to a date in S is null, if you use a
text character you can change the formula to

=AND(A3+7<=TODAY(),NOT(ISNUMBER(S3)))

--

Regards,

Peo Sjoblom

lcb369 said:
I have a spreadsheet with 20 columns, each containing various types of
Click to expand...
data. Column A is Date Received. Column S is Date Returned. Am trying,
unsuccessfully, by using advanced filter, to have the sheet display all
records where date received is 7 days or more before "todays" date ONLY IF
the Date Returned column does not contain a date. The display is in annother
part of the sheet. How can I get the thing to do that? Any suggestions?
 
Back
Top