SOLVED

Formula to locate the date from a list of dates that is less than but closest to a specific date

Copper Contributor

Formula to locate the date from a list of dates that is less than but closest to a specific date

I have a column of specific dates (sprint end dates). When determining delivery milestone dates for a project, I need to know which sprint a milestone date falls in. I can look it up manually but where is the fun in that?

For example: if a project estimate determines a design must be completed by 11/10, I want to know the sprint end date of the sprint it would fall into?

 Data Table: Sprint Start Date End Date OCT24-21 10/9/2024 10/22/2024 OCT24-22 10/23/2024 11/5/2024 NOV24-23 11/6/2024 11/19/2024 NOV24-24 11/20/2024 12/3/2024 DEC24-25 12/4/2024 12/17/2024 DEC24-26 12/18/2024 12/31/2024 Milestone date: 11/10/2024 Sprint End Date: 11/19/2024

4 Replies

Re: Formula to locate the date from a list of dates that is less than but closest to a specific date

You can use SUMPRODUCT to obtain the sprint end date:

``=SUMPRODUCT((B9>=B2:B7)*(B9<=C2:C7)*C2:C7)``

Re: Formula to locate the date from a list of dates that is less than but closest to a specific date

Thank you!! That works perfectly!

Re: Formula to locate the date from a list of dates that is less than but closest to a specific date

You're welcome. Have a good weekend!
best response confirmed by JillF1015 (Copper Contributor)
Solution

Re: Formula to locate the date from a list of dates that is less than but closest to a specific date

Since you didn't state the version of Excel you use, here is a solution designed for 365

``= XLOOKUP(milestone, startDate, endDate,,-1)``
1 best response

Accepted Solutions
best response confirmed by JillF1015 (Copper Contributor)
Solution

Re: Formula to locate the date from a list of dates that is less than but closest to a specific date

Since you didn't state the version of Excel you use, here is a solution designed for 365

``= XLOOKUP(milestone, startDate, endDate,,-1)``