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

Victoria Curry

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

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.