Date Formula Help!
I am tracking target start and target end dates within a sheet. I'm trying to derive each month that a project is actively being worked on (so if the target start was 2/1/22 and the target end was 4/30/22), I'm trying to build a formula that would generate "02, 03, 04" Any thoughts how to write this?
Answers

This is a tough one. You need to get the month number for the start month, the end month, and all the months in between. So the first thing to do is determine if there are more than 1 month, and also if there are any months in between the start and end dates and what they are.
=IF(AND(YEAR(Start@row) = YEAR(End@row), MONTH(Start@row) = MONTH(End@row)), "0"+MONTH(Start@row), IF(AND(YEAR(Start@row) = YEAR(End@row), MONTH(End@row) MONTH(Start@row) =1), ""0"+MONTH(Start@row)+", "+"0"+MONTH(End@row)", IF(AND(YEAR(Start@row) = YEAR(End@row), MONTH(End@row)  MONTH(Start@row) =2), ""0"+MONTH(Start@row)+", "+"0"+(MONTH(Start@row)+1)+", "+"0"+MONTH(End@row)", IF... and so on. Have to also account for change in year, like if it starts in November and goes until February... So you may want to employ some hidden helper columns to determine those factors.
Like I said  it's a tough one!
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!