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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!