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 @O.Nash,

Bit of a throwback, but it'd probably be easier to do something like this (again, using Jan 2023 as an example):

=IF(AND([Start Date]@row < DATE(2023, 2, 1) - 1, [End Date]@row >= DATE(2023, 1, 1)), "Yes", "")

Start Date should be less than the 1st of the following month and end date equal to/greater than date of the 1st of the current month.

For December you'd also need to update the year:

=IF(AND([Start Date]@row < DATE(2024, 1, 1) - 1, [End Date]@row >= DATE(2023, 12, 1)), "Yes", "")

Example doing this (cutting out a lot of months just because changing the formula a lot for just a test is effort!):

Hope this helps, but if you encounter any problems/issues then just let us know! 😊

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.

O.Nash

@Nick Korna Could you elaborate on how you would convert to AND statements and add the Year please? and Thank you :-) - I've been trying and failing to adapt the formula to include the year.

https://community.smartsheet.com/discussion/comment/375307#Comment_375307

Nick Korna

Hi @O.Nash,

Bit of a throwback, but it'd probably be easier to do something like this (again, using Jan 2023 as an example):

=IF(AND([Start Date]@row < DATE(2023, 2, 1) - 1, [End Date]@row >= DATE(2023, 1, 1)), "Yes", "")

Start Date should be less than the 1st of the following month and end date equal to/greater than date of the 1st of the current month.

For December you'd also need to update the year:

=IF(AND([Start Date]@row < DATE(2024, 1, 1) - 1, [End Date]@row >= DATE(2023, 12, 1)), "Yes", "")

Example doing this (cutting out a lot of months just because changing the formula a lot for just a test is effort!):

Hope this helps, but if you encounter any problems/issues then just let us know! 😊

O.Nash

@Nick Korna YOU ARE A LEGEND!!! Thank you so much. That worked for me absolutely brilliantly! I would never have got there on my own!