• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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 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)
 
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.

83529
 

Attachments

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