Populate Q1, Q2, Q3 etc as per Start date and End Date

Options

Hi Team,

I have below 2 requirements -

1) Populate Q1, Q2 Q3 etc as per Start date and End Date, I am using the below formula but it is giving error

=IF(AND([Quarter Start Date]@row >= DATE(2020, 8, 1), [Quarter Start Date]@row <= DATE(2020, 10, 31), "Q1'21", IF(AND([Quarter Start Date]@row >= DATE(2020, 11, 1), [Quarter Start Date]@row <= DATE(2021, 1, 31), "Q2'21"))))

The error I am facing is Incorrect Argument.

Attached the screenshot for your reference.

2) Once this is resolved another requirement is, In the Smartsheet B I want to pull the Qtr if the Created date falls between the Start date and end in Smartsheet A.

Please note Created date is the system generated Column.

Can you please support to resolve these 2 queries.

Thanks

Anil Raghav

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You just have a couple of closing parenthesis out of place in your formula. You didn't close out your AND functions is all.

    =IF(AND([Quarter Start Date]@row >= DATE(2020, 8, 1), [Quarter Start Date]@row <= DATE(2020, 10, 31)), "Q1'21", IF(AND([Quarter Start Date]@row >= DATE(2020, 11, 1), [Quarter Start Date]@row <= DATE(2021, 1, 31)), "Q2'21"))


    For the second part, are you able to provide a screenshot with some mocked up/manually entered data that shows what you are trying to accomplish?

  • anilkrag1984
    Options

    Hi Paul Thanks I have resolved both the queries but if you help me with the other one.

    I have 2 Smartsheets as Smartsheet A and B

    In Smartsheet A I want to populate RGY in such way that if Value in Smartsheet B corresponding Theatre and Qtr as below

    For ex if ANZ Q1 value in Customer Training Column in Smartsheet B is 8% then in Smartsheet A it should reflect Red

    same as below

    • 0-33% Red
    • 33-66% Amber
    • 67 – 100% Green


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I'm not sure I follow. Can you provide a screenshot with manually entered data to show what you are trying to accomplish?

  • anilkrag1984
    Options

    In Smartsheet A I need to populate RGY in Column Intersight Activation based on the data in the other Smartsheet B. Same columns in both the sheet. Reference to be used as Theatre and Quarter

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    We would start by using an INDEX/COLLECT to pull the appropriate percentage.


    =INDEX(COLLECT({Sheet B Percentage Column}, {Sheet B Thatre Column}, Theatre@row, {Sheet B Quarter Column}, Quarter@row), 1)


    Then we can use that in a nested IF to say that if it is less than 33%, "Red". If less than 66%, "Amber". Otherwise, "Green".


    =IF(INDEX(COLLECT(..........)<= .33, "Red", IF(INDEX(COLLECT(..........)<= .66, "Amber", "Green"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!