Return "Yes" if a date range includes a certain month


I am trying to return either a "Yes" or "No" value in columns for the months of the year if a date range for a project or activity has dates in that month. For example, if a project starts on 2/15/2022 and ends 4/15/2022, I would want to have "Yes" in the February, March, and April columns and "No" in all other month columns for the year. I have tried multiple formulas to try and achieve this, but so far have been unsuccessful.

Has anyone had success with something like this?

I feel like I need several nested OR statements, but can't seem to get my head around it and the closest to success I've been was simply to get a "Yes" in the month for only the start and end dates (while all the months covered by the date range had an incorrect "No").


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Victoria Curry,

    If your months are in the same year this is certainly doable.

    For your January column:

    =IF(OR(MONTH([Start Date]@row) = 1, MONTH([End Date]@row) = 1, OR(AND(1 - MONTH([Start Date]@row) > 0, MONTH([End Date]@row) - 1 > 0))), "Yes")

    In the following months, substitute the bold numbers in the formula for the relevant month number (2 for February, 3 for March etc.).

    Example (only using a few months):

    If your project dates and columns spanned several years (e.g. Jan 2023) then you would have to make these statements into AND ones and include the year.

    Hope this helps!

  • Victoria Curry

    Hi Nick,

    Thanks for a great response. Unfortunately the months are not always in the same year. The sheet is meant to cover a fiscal year, which for us starts in October and ends in September, which means it will always span 2 calendar years. That has been part of my major challenges with figuring out a solution.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!