Jan 31 2022 07:34 AM
Hello all,
I am sure this is an easy formula, but it is escaping me... I have a spreadsheet which is used to track open assignments. Each row displays assignment number, Date opened, date closed, and status (Open or Closed). I am looking to have a line to show the oldest assignment which is still Open based on all the Assignments within the spreadsheet. So if there are 1500 assignments, I would like to know the single oldest assignment number.
Jan 31 2022 07:50 AM
The following formula should work in all versions of Excel, but if you don't have Office 2021 or Microsoft 365, you'll have to confirm the formula with Ctrl+Shift+Enter.
I assumed that Assignment Number = column A, Date Opened = column B, Date Closed = column C and Status = column D, with data starting in row 2.
=INDEX(A2:A1500,MATCH(1,(B2:B1500=MIN(IF(D2:D1500="Open",B2:B1500)))*(D2:D1500="Open"),FALSE))
Jan 31 2022 08:13 AM
Jan 31 2022 08:15 AM
Solution=INDEX(A2:A1500,MATCH(1,(D2:D1500=MIN(IF(G2:G1500="Open",D2:D1500)))*(G2:G1500="Open"),FALSE))
Remember, confirm with Ctrl+Shift+Enter if you're not on Microsoft 365 or Office 2021.
Jan 31 2022 08:25 AM
Jan 31 2022 08:58 AM
Jan 31 2022 09:00 AM
Jan 31 2022 09:18 AM
@Darkdata , you are welcome
Jan 31 2022 08:15 AM
Solution=INDEX(A2:A1500,MATCH(1,(D2:D1500=MIN(IF(G2:G1500="Open",D2:D1500)))*(G2:G1500="Open"),FALSE))
Remember, confirm with Ctrl+Shift+Enter if you're not on Microsoft 365 or Office 2021.