Lookup date between two dates and return value

Caitlin Gaines

"Schedule" Helper Sheet: This sheet has the schedule of each sprint

Columns: Sprint, Start Date, Week End Date

Example Data Set:

Sprint 1 | 3/14/2022 | 3/18/2022

Sprint 1 | 3/21/2022 | 3/25/2022

Sprint 2 | 3/28/2022 | 4/1/2022

Sprint 2 | 4/4/2022 | 4/8/2022

Objectives/Milestones "Roadmap" Sheet:

Columns: Objective/Milestone, Set Start Date, Set End Date, Sprint Due

Example Data Set:

Obj - Milestone 1 | 3/14/2022 | 3/16/2022 | {formula should return Sprint 1}

Obj - Milestone 2 | 4/6/2022 | 4/6/2022 | {formula should return Sprint 2}

In the sprint due column, I need a formula that returns the sprint from "Schedule" based on which sprint the 'set end date' the obj-milestone falls within from the "Roadmap".

I've used a formula with INDEX, COLLECT, but it's only returning and exact match where the set end date is explicitly listed on the schedule helper sheet.

Find more posts tagged with

Accepted answers

All comments

Julio S.

Hi @Caitlin Gaines,

If your formula returns the desired Sprint only when there is an exact match, that means that the operator used only looks for exact matches (=). I have recreated your environment and the following formula should return the first Sprint listed that has a date = or over the End Date in the relevant row:

=INDEX(COLLECT({Sprint}, {Week End Date}, >=[Set End Date]@row), 1)

I'm also attaching captures of each cross-sheet reference for your own reference

I hope this can be of help.

Cheers!

Julio

TLH

A little crude, but works:

First, create a folder to contain your project plan and a newly-added second page.

Create a new second page. I called mine "Sprint & Holiday Schedule".
In this second page, add three columns (from left to right): Sprint Date, Sprint, Holidays.
NOTE: Holidays column is optional.
Mine looks like this:

Second, in your main project plan:

Add 2 columns: Sprint Start, Sprint End.
In Sprint Start cell, use VLOOKUP: =VLOOKUP([Latest Start]@row, {Copy of Sprint & Holiday Schedule Range 1}, 2, true). Apply formula to Sprint End.
"2" = column 2 containing sprint number.
Mine looks like this:

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.