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

Debra Roland

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")

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.

Debra Roland

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.

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

Debra Roland

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

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☺️