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
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!