My formula with reference to another sheet is no longer working.

It was working fine and then just seemed to break. Below is my formula. I'm doing check for PTO which we maintain in a dif smartsheet.

=IF(OR(COUNTIFS({RDI Calendar Range 1}, [Assigned To]@row, {RDI Calendar Range 2}, >=Start@row, {RDI Calendar Range 3}, <=Finish@row) > 0, COUNTIFS({RDI Calendar Range 1}, [Assigned To]@row, {RDI Calendar Range 2}, <=Start@row, {RDI Calendar Range 3}, >=Finish@row) > 0), "Unavailable", "Available")

Tags:

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Debra Roland 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam, Thanks so much for your response! This issue only seem not to work for one assignee. All others works. Here are some screen shots below. The first two are of the Smartsheet with the formula. The second it the Smartsheet referenced in the countifs. It's our PTO calendar. Mia has PTO 4/26 - 4/30. The formula should flag as unavailable for the task on line 29.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Debra Roland

    In these images, Mia doesn't match the criteria that your formula is looking for.

    Her PTO Start Date is greater than the task's Start Date

    and

    Her PTO End Date is greater than the task's End Date


    Your formula is only comparing the Start Date with Start Date and End Date with End Date, without cross-comparing the Start and Ends with each other. The issue here is that her PTO Start Date is the same as her task End Date!

    You may want to add two more COUNTIFS statements to your formula, checking:

    • Task End with the PTO Start

    COUNTIFS({RDI Calendar Range 1}, [Assigned To]@row, {RDI Calendar Range 2}, =Finish@row) > 0

    • PTO End with the Task Start

    COUNTIFS({RDI Calendar Range 1}, [Assigned To]@row, {RDI Calendar Range 3}, =Start@row) > 0


    Try this:

    =IF(OR(COUNTIFS({RDI Calendar Range 1}, [Assigned To]@row, {RDI Calendar Range 2}, >=Start@row, {RDI Calendar Range 3}, <=Finish@row) > 0, COUNTIFS({RDI Calendar Range 1}, [Assigned To]@row, {RDI Calendar Range 2}, <=Start@row, {RDI Calendar Range 3}, >=Finish@row) > 0, COUNTIFS({RDI Calendar Range 1}, [Assigned To]@row, {RDI Calendar Range 2}, =Finish@row) > 0, COUNTIFS({RDI Calendar Range 1}, [Assigned To]@row, {RDI Calendar Range 3}, =Start@row) > 0), "Unavailable", "Available")


    Let me know if this works for you!

    @Bassam.M Khalil Let me know if you see anything I missed 🙂


    Cheers,

    Genevieve

  • Thank you Genevieve so much for your help! I will try your suggestion and let you know how it works for me.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Genevieve P 

    Hope you are safe and well, Your answer is excellent and you have covered all the points. I'm still learning from the amazing solutions that you are adding to the SmartSheet community☺️

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"