Forum Discussion

MarkBeck54's avatar
MarkBeck54
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" , equal  to today as either "paid" or "payment due". Thanks

excel

3 Replies

  • m_tarler's avatar
    m_tarler
    Bronze 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's avatar
      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's avatar
        m_tarler
        Bronze 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.