Display the text of Active Months based on a date range

Kim Johnson

I hope someone can help me here!

I am trying to display a list of active months based on a date range.

The screen shot just shows a manual selection. But since the date entry is automated using a connector, I would like to also automate the Active Project Months to prevent misalignment.

Here is the path I took, but got stuck.

1. Find difference in months between start and end: =((YEAR([Project Contract End Date]@row) - YEAR([Project Start Date]@row)) * 12) + MONTH([Project Contract End Date]@row) - MONTH([Project Start Date]@row)
2. Determine Start Month and Year: =IF([Project Start Date]@row <> "", IF(MONTH([Project Start Date]@row) = 1, "January", IF(MONTH([Project Start Date]@row) = 2, "February", IF(MONTH([Project Start Date]@row) = 3, "March", IF(MONTH([Project Start Date]@row) = 4, "April", IF(MONTH([Project Start Date]@row) = 5, "May", IF(MONTH([Project Start Date]@row) = 6, "June", IF(MONTH([Project Start Date]@row) = 7, "July", IF(MONTH([Project Start Date]@row) = 8, "August", IF(MONTH([Project Start Date]@row) = 9, "September", IF(MONTH([Project Start Date]@row) = 10, " October", IF(MONTH([Project Start Date]@row) = 11, "November", IF(MONTH([Project Start Date]@row) = 12, "December"))))))))))))) + " " + YEAR([Project Start Date]@row)

From here, I was hoping to use a helper sheet that has a list of the Month Year somehow look up the "First Month Year" in that list, then collect the number of cells below it that match the Count of Months. I am stuck on how to do this though. Is it possible, or are there any other clever approaches to this? Thank you!

Paul Newcome

I would suggest a reference table. Have numbers going down one column that represents the year and month in yyyymm format.

202301

202302

202303

202304

so on and so forth.

Then in another column you would list out the text version of each year/month combo.

Finally the formula to grab all of the month/year text strings you want displayed would be:

=JOIN(COLLECT({Reference Table Text Column}, {Reference Table yyyymm Column}, AND(@cell>= VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row)< 10, "0", "") + MONTH([Start Date]@row)), @cell<=VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row)< 10, "0", "") + MONTH([End Date]@row)))), CHAR(10))

StevenBlackburnMBA

1. You could do this a couple different ways, but one that comes to mind is creating the helper sheet with each row listed under a column from the first date of your first project to the last date of your final project. For example: Row 1: 01/2021, Row 2: 02/2021, Row 3: 03/2021... and so on and so on...
2. Then, you could use that helper sheet to determine if the IF a project starts in 04/2021 (who really cares about the exact DAY when determining the active months in terms of this particular scenario as if the project starts in that month, it was active that month), then show all items between 04/2021 and whatever the end date of the project is.
3. In this instance, since you are using a month/year, this is done numerically, and you can further expand your formula to display (IF 02/2021 is presented, display the numbers before the forward slash and then assign that number a month...) -- note that alot of times, you can use that helper sheet to INDEX/MATCH the dates and display months that you display in columns next to that row and include them in your range (options for creativity can be pretty endless here dependent on how you want to view the info)
4. From there, you can display any items between the ranges that you find and display those using your formula

I am always happy to hop on a call and explain this in more detail or help go through a screen share, as it always makes a little more sense when you can explain it, but I'm hoping this can atleast get you started. We have done some pretty extensive formulas for some of our systems that require parsing out items typed in one field to show similar data, so I'd assume this would be a pretty similar approach.

Kim Johnson

Thanks so much for your response!

I think I am tracking on your approach, but when you say "show all items between 04/2021 and whatever the end date of the project is", what approach would I take here? I can find the first month/year, but I am stuck on getting the following month/years to display based on the project end date.

I appreciate your offer to hop on a call and screen share. Can I send an invite to your email listed in your profile?

Kim Johnson

Hey Smartsheet Community,

Still looking for some help on this.

Any other ideas or further clarification?

Paul Newcome

I would suggest a reference table. Have numbers going down one column that represents the year and month in yyyymm format.

202301

202302

202303

202304

so on and so forth.

Then in another column you would list out the text version of each year/month combo.

Finally the formula to grab all of the month/year text strings you want displayed would be:

=JOIN(COLLECT({Reference Table Text Column}, {Reference Table yyyymm Column}, AND(@cell>= VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row)< 10, "0", "") + MONTH([Start Date]@row)), @cell<=VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row)< 10, "0", "") + MONTH([End Date]@row)))), CHAR(10))

Kim Johnson

Thank you so much Paul! That worked beautifully!

Paul Newcome

Happy to help. 👍️