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

Formula for the most recent date prior to last date

Mix1325

New Member
Hello,

I would appreciate if anyone can help with a formula that would solve the following task.

The last purchase of a material at a particular plant was made on 11/30/2018. There were also two other previous purchases (i.e., for the same material & plant) made on 8/25/2018 and 10/5/2018. I need a formula that would provide the most recent month and year prior to the last purchase. In the case provided above, the result should show Oct-18 (the date format needs to be the same as provided "Oct-18"). Help columns are allowed if needed. Please see attachment for the same example in excel.

Feel free to ask questions if additional details are needed. Thank you in advance for all your help and support in this matter.
 

Attachments

John Jairo V

Well-Known Member
Hi, Mix1325!

You could try this formula:
[I3] : =TEXT(AGGREGATE(14,6,Table1[Purchase Date]/(Table1[Plant]=G3)/(Table1[Material]=H3),2),"mmm-yy")
or
[I3] : =TEXT(AGGREGATE(14,6,Table1[Purchase Date]/(Table1[Plant]&Table1[Material]=F3),2),"mmm-yy")

Blessings!
 

Mix1325

New Member
John, I just realized that I forgot to include one of the most important criteria. If two purchases were made in the same month, the formula should show the purchase occurred prior to that month as opposed to listing the same month as the previous purchase. I have adjusted the file to show this situation.
 

Attachments

Top