IF/AND Formula not working for Date falling within a specific Month

Hi, I am struggling to get an IF/AND formula to work correctly, and hope someone can help me.

The formula is in a text/number column labeled "Oct". I need the formula to look at the date column labeled "Proposed Start Date" and the date column labeled "Proposed End Date". The proposed start date and proposed end date represent the duration of a project. I need to have the formula return the value in the text/number column labeled "Total Team" when the project will be active in Oct.

My formula is:

=IF(AND($[Proposed Start Date]@row <= DATE(2023, 10, 1), $[Proposed End Date]@row >= DATE(2023, 10, 31)), $[Total Team]8, "")

For example, the formula is working correctly for the duration data range of 06/26/23 through 04/12/23 (shown in the first row.) But it isn't for the range 10/16/23 through 05/17/24 and 10/30/23 through 01/26/24 (shown in the red boxed cells.)

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!