Forum Discussion
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 |
Since you didn't state the version of Excel you use, here is a solution designed for 365
= XLOOKUP(milestone, startDate, endDate,,-1)
You can use SUMPRODUCT to obtain the sprint end date:
=SUMPRODUCT((B9>=B2:B7)*(B9<=C2:C7)*C2:C7)Please see attached sample workbook.
- Thank you!! That works perfectly!
- You're welcome. Have a good weekend!
Since you didn't state the version of Excel you use, here is a solution designed for 365
= XLOOKUP(milestone, startDate, endDate,,-1)
