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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Are you able to provide a screenshot of the main sheet for reference?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you Paul, that worked like a charm.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!