Compare dates in two different sheets and return a value

I have a reporting use case where the project managers have to enter a reporting code so that reporting roll up occurs in the correct fiscal quarter. I want to automate the assignment of the reporting code so project managers do not have enter the code . I created a sheet that contains a reporting code for fiscal years and quarters, and then want to test the projected start date against the reporting code start date. So essentially,

if {reporting code start date} greater than or equal to [Projected Start Date]@ row, return the corresponding reporting code.

Same for

if {reporting code end date} less than or equal to [Projected End Date]@ row, return the corresponding reporting code.

Screen shot of the fiscal year/quarter table is below:

Any help is truly appreciated.

Regards,

Janet

Find more posts tagged with

Accepted answers

Paul Newcome

Assuming you are wanting to populate "22Q2" there on row 1 because the Projected Start is 10/01/21, you are going to want something like this...

=INDEX({Table Sheet Code Column}, MATCH(MAX(COLLECT({Table Sheet Start Column}, {Table Sheet Start Column}, @cell<= [Projected Start]@row)), {Table Sheet Start Column}, 0))

All comments

Paul Newcome

Are you able to provide a screenshot of the main sheet for reference?

Janet Skjerven

Hi Paul, my apologies for failing to include a screenshot of the main plan, see below.

Paul Newcome

Assuming you are wanting to populate "22Q2" there on row 1 because the Projected Start is 10/01/21, you are going to want something like this...

=INDEX({Table Sheet Code Column}, MATCH(MAX(COLLECT({Table Sheet Start Column}, {Table Sheet Start Column}, @cell<= [Projected Start]@row)), {Table Sheet Start Column}, 0))

Janet Skjerven

Thank you Paul, that worked like a charm.

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.