Formula to identify whether or not a project starts or ends in a Quarter

Options
Pamelam
Pamelam ✭✭
edited 04/06/22 in Formulas and Functions

Hello,

I have columns for each quarter of the year (Q1, Q2, etc.) in Checkbox format. I am trying to create a formula where the Checkbox is automatically checked if the Start Date and/or End Date fall within the quarter.

For Q1, I only need to look at the start date because all of the Start Dates are in 2022.

=IF(AND([Start Date]@row >= DATE(2022, 1, 1), [Start Date]@row <= DATE(2022, 3, 31)), true, false)

I run into trouble for the Q2 where the project may have started in Q1 but will run into Q2, so I need to consider the End Date as well. I tried the following as well as other variations but I get the #incorrect argument error.

=IF((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), true, IF(AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30))), true, false)

Please let me know if I need more or less parenthesis, or if I need an OR thrown in there. Thank you in advance.

Best Answer

  • Pamelam
    Pamelam ✭✭
    Answer ✓
    Options

    OK, l solved it and am just leaving this for others if they need it. Instead of putting the End Date in the False section, I used OR

    =IF(OR((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), (AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30)))), true, false)

Answers

  • Pamelam
    Pamelam ✭✭
    Options

    Almost as soon as I posted this, I solved half of my problem. I added a second IF at the beginning. This works for the rows that have Start and End Dates completely within Q2, but any rows that start and/or end outside of Q2 still give the #Incorrect Argument.

    =IF(IF(AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), true, IF(AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30))), true, false)

    Again, thank you for any assistance.

  • Pamelam
    Pamelam ✭✭
    Answer ✓
    Options

    OK, l solved it and am just leaving this for others if they need it. Instead of putting the End Date in the False section, I used OR

    =IF(OR((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), (AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30)))), true, false)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!