Date Formula Help!

Options

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!