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
Best Answer
-
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))
Answers
-
Are you able to provide a screenshot of the main sheet for reference?
-
Hi Paul, my apologies for failing to include a screenshot of the main plan, see below.
-
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))
-
Thank you Paul, that worked like a charm.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!