# Assistance with formula to display items 2 days before the expiring date.

#### Fred Wayne

##### Member
Good afternoon, I made a simple inventory type sheet, and you will see that it needs the sheet called "DATE STATUS", to show me the list of the products that will soon be out according to the date, 2 days before the out date. For example, in the "DATA" sheet there are several products and each one has a different departure date, that is, the day that is already its due date. I need to know if it is possible that the "Date Status" sheet shows the products that almost left 2 days before their departure date, taking into account the current date. To give an example, if today is 21, then 23 should be displayed if today is 22, 24 should be displayed or if today is 25, 27 should be displayed. Is there a formula that allows us to do this?
Thank you so much in advance.

#### Attachments

• BOOK OF SALES.xlsm
14.4 KB · Views: 10
If you have the correct XL version enter =FILTER(Table1;Table1[DATE]=TODAY()+2) inA3
(BTW do NOT use merged cells (E1 and F1) but enter the date in E1, select E1 and F1 and format as " Center across selection"). Merged cells are nothing but trouble

I forgot to menytion that I am using office 2019 64Bits-, it shows me the error "That function is not valid"

I saw this formula in internet, however, I cannot manage to make it if to my needs, How can I make it fit to my sheet "DATE STATUS"? This is the formula: =IFERROR(INDEX(DATA!\$A\$2:\$D\$154,SMALL(IF((DATA!\$A\$2:\$A\$154>=\$B\$1)*(DATA!\$A\$2:\$A\$154<=\$D\$1),ROW(DATA!\$A\$2:\$A\$154)-ROW(DATA!\$A\$2)+1),ROWS(\$A\$4:B4)),COLUMNS(\$A\$4:B4)),"")

AFAIK FILTER is available in 2019 BUT I forget to adapt the formula to US locale ( I left the semi colon instead of making it a comma)
Try =FILTER(Table1,Table1[DATE]=TODAY()+2)

It keeps giving me the same issue, " That Function isn't valid"

No problem, I'll search on the web for a solution. Thank you so much for your time.

Check out around rows 3 and 20 of sheet DATE STATUS in the attached. Top one looks for 2 days exactly, bottom one looks for a range of dates.
You'll probably have to array-enter these formulae in more rows than I have.
Note I've hard coded the date in cell E1 but you'll need to revert it to =TODAY() to use it in real life.

#### Attachments

• Chandoo51959BOOK OF SALES.xlsm
17 KB · Views: 4
I saw this formula in internet, however, I cannot manage to make it if to my needs, How can I make it fit to my sheet "DATE STATUS"? This is the formula: =IFERROR(INDEX(DATA!\$A\$2:\$D\$154,SMALL(IF((DATA!\$A\$2:\$A\$154>=\$B\$1)*(DATA!\$A\$2:\$A\$154<=\$D\$1),ROW(DATA!\$A\$2:\$A\$154)-ROW(DATA!\$A\$2)+1),ROWS(\$A\$4:B4)),COLUMNS(\$A\$4:B4)),"")
Display items 2 days before the expiring date (for Excel 2016)

I've also hard coded the date in cell E1 in 3/20/2023 and you'll need to revert it to =TODAY() as per your requirement.

In A3, formula copied across right and down until blank:

=IFERROR(INDEX(DATA!\$A\$1:\$N\$100,AGGREGATE(15,6,ROW(DATA!\$A\$1:\$A\$100)/(DATA!\$A\$1:\$A\$100=\$E\$1-2),ROW(\$A1)),COLUMN(A\$1)),"")

Remark: I used Aggregate function instead of Small+If function to avoid the array "Ctrl+Shift+Enter" entry.

#### Attachments

• BOOK OF SALES (BY).xlsm
16.5 KB · Views: 5
Last edited: