Formula to retrieve a term code based on a date range

Hello everyone,

I need a formula that will retrieve a Term Code from a helper sheet based on the date that a row is processed and insert it into a worksheet. The helper sheet has the Term Codes and it has columns for Start and End dates that apply to each Term Code. For example, the Term Code "2217" is valid for the period 8/23/21 - 1/9/22. Term Code "2223" is valid for the period 1/10/22-5/8/22.

I want the Term Code to populate automatically based on a Process Date in a separate worksheet. In my example, the Process Date is 12/14/21. If the formula works correctly, this date is within the range for Term Code 2217 and should return "2217" in the Process Term column.

Screen Shot 2021-12-29 at 5.19.08 PM.png

Screen Shot 2021-12-29 at 5.19.59 PM.png

Is there a formula that can look at a date range and extract the correct Term Code based on the Process Date?

Thanks for considering this, Jill Freuden, WSU

Find more posts tagged with

Accepted answers

All comments

Heath Hilton

Jill Freuden,

I have spent some time with this and am having trouble getting it because even with VLOOKUP (which is the best shot I can think of), you would need to be searching for a specific cell value instead of something within a range. Would you be able to add in an extra cell (even if it is hidden) that does a bit of math for us? If you used it to do take the [Process Date] and translate that into the [Last Process Date], you could then search the other sheet for that value and pull in the Term Number that you are looking for.

Let me know how I can help more even if we do a screen sharing session; my Dad is an alum and would be pleased to hear that I help you out.

Jill Freuden

Hi Heath,

I tried to think of a helper formula that would get me closer and hadn't considered the idea of translating the process date. I can't think of how you'd translate the date, so I would love a screenshare, if you're free. I can be reached at [first name].[last name]@wsu.edu.

I use INDEX/MATCH more than VLOOKUP these days; it's a better fit when more than one person has access to my helper sheets. I tried setting up a helper column [TLPD] that uses INDEX/MATCH to pull the [Last Process Date] based on the [Term] (vs. the [Process Term]) then added the formula to compare dates "=IF([Process Date]@row < TLPD@row, "2217", "Jill Fix")" but that requires me to manually fix everything from a previous term.

I also thought of hiding the current process term in the form that the departments fill out to submit these grade changes. It's easy, but requires me to remember to update it each term.

Thank you for reaching out!

Jill

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.