Forum Discussion

Copper Contributor

Apr 02, 2026

Shop Inventory

Hi all I trying to find a solution to the following :Have a list of Dates from past present and future I want to return orders older than 7 days "No Order",Orders less than 7 days old as "pending" ,...

excel

m_tarler

Silver Contributor

Apr 02, 2026

having a sheet would help but let's say the data is in Table1 and the date is in a column Date and the other column of interest is Status so Table1[Date] and Table1[Status] then

=FILTER( Table1, 
               (Table1[Date]<TODAY()-7)*(Table1[Status]="No Order") + 
               (Table1[Date]>TODAY()-7)*(Table1[Status]="Pending") + 
                (Table1[Date]=TODAY())*((Table1[Status]="paid")+(Table1[Status]="payment due") )
               , "none found")

so basically in the conditional part of the Filter I 'add' each condition to act as an 'OR' and 'multiply' each condition to act as an 'AND'

MarkBeck54

Copper Contributor

Apr 02, 2026

Thank you for your reply I dont have these held in table at the moment the result is dependant on the MAXIFS function

m_tarler
Silver Contributor
Apr 02, 2026
Again, having more information like the actual sheet layout and formulas and desired output would help.
You say "I dont have these held in table" but where are they? What does "result is dependant on the MAXIFS" mean? I image you have some sort of list of dates and statuses as you say "Have list of Dates". My example uses reference to a range of data that is formatted as a table but there is no reason you can't substitute the corresponding range(s). so for example if you have:
A B C
1 Date Status
2 1/1 Pending
3 1/2 No Order
4 1/3 paid

so "Table1" in this case would be A2:B4 and "Table1[Date]" would be A2:A4 and "Table[Status]" would be B2:B4
but using the names is much easier to read and you can substitute your particular ranges accordingly.