I have an excel sheet where batches consist of 2 or more raw materials with each a serial number. Those serial numbers have each their own expiry date. I want the final expiry date (column D) based on the oldest expiry date of the raw materials.
For example: batch 1 has 25635 (expiry date 11-2019) and 12345 (expiry date 07-2019). I want that column D shows only the oldest expiry date, which is in this case 07-2019
How do I do that? (the excel sheet is in the attachment)
You may use such Array formula:
Formula:
Please Login or Register to view this content.
But the dates in column L shall be dates, not texts. See in attached file how is a date formatted with custom format to display only month and year
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. (on Mac I think it is CTRL + SHIFT + RETURN). If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Best Regards,
Kaper
Thank you!!
Just one more question, how can you make this work with merged cells?
For example, for the selected cell in the attachment, the code should refer to F3:I4, since F3:F5 is a merged cell, but that doesn't work. And if you do F4:I4, it ignores the expiry date of F3:F5 cell
Attachment 677726
Selecting Attachment 677726 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
To show an example of the issue please utilize the instructions in the banner at the top of the page.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Attachment 678066
How about now?
There are instructions at the top of the page explaining how to attach your sample workbook.
You have a Mac, so I am moving this thread to the Other Platforms section.
Oh, and DON'T USE MERGED CELLS!!!! Don't put form before function.
Last edited by AliGW; 05-17-2020 at 03:33 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I have now the excel sheet attached
Had a look - the advice stands: don't use merged cells.
Allright, thanks, haha
No problem.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks