Date Formula Help!

stephanie.blake67241

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?

Jeff Reisman

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!