Indicating Each Month A Project Is Active?
Community,
I'm struggling to indicate which months a project is active given a project start and end date. If a project starts on 09 January 2020 and ends on 09 March 2020, it would be active January, February, March. I would like to represent this as follows:
Showing that the project was active in its Start Month and End Month is easy (as in Projects A and B above). For example, I can use...
=IF(OR(MONTH([Project Start Date]1) = 1, MONTH([Project End Date]1) = 1), 1, 0)
...to do this for January.
Where i'm stuck is how to determine the intervening months (as in Projects C and D above).Any ideas?
Thanks in advance.
Best Answers
-
Paul Newcome ✭✭✭✭✭✭
You would need to use an IF/AND statement to determine if
Start Date is less than or equal to the last day of the month
AND
End Date is greater than or equal to the first day of the month.
So for January you would use something along the lines of
=IF(AND([Project Start Date]@row <= DATE(2020, 1, 31), [Project End Date]@row >= DATE(2020, 1, 1)), 1)
-
Mike Wilday ✭✭✭✭✭✭
@Paul NewcomeThanks for noticing the missing parenthesis. I edited my original post to include it.
And you're right projects that extend over multiple years would be much more complicated, but I think that it could still be done by comparing the year and month. If you actually used the last date of the month you were checking against you could just compare the dates altogether.
=IF(AND(Date(2019,02,1) >= [Project Start Date]@row, Date(2019,02,27) <= [Project End Date]@row, 1)
@RKAre you looking at a year's work at one time or would this project extend over multiple years? IF my above solution works for you then please mark it as a correct answer.
Answers
-
Mike Wilday ✭✭✭✭✭✭
Are you concerned about indicating the specific months? or are you more interested in getting a count of total months that a specific project was active? I could imagine that you could check to see if the current month number was greater than the start month number and less than the end date month number.
For February you could try something like
=IF(AND(2 >= Month([Project Start Date]@row), 2 <= Month([Project End Date]@row), 1)
If that works, then you could essentially do that for all the rest of the columns.
-
Mike Wilday ✭✭✭✭✭✭
Yep, just tested out my theory. That will work for you. Just change the 2 to whichever month number that your are looking to add the 1 flag too.
-
Paul Newcome ✭✭✭✭✭✭
You would need to use an IF/AND statement to determine if
Start Date is less than or equal to the last day of the month
AND
End Date is greater than or equal to the first day of the month.
So for January you would use something along the lines of
=IF(AND([Project Start Date]@row <= DATE(2020, 1, 31), [Project End Date]@row >= DATE(2020, 1, 1)), 1)
-
Paul Newcome ✭✭✭✭✭✭
I just realized my solution is pretty much the same as@Mike Wilday's. The only thing I would change for his would be fixing the second MONTH function to include the appropriate parenthesis.
=IF(AND(2 >= Month([Project Start Date]@row), 2 <= Month([Project End Date]@row)), 1)
另外这只会工作,如果他们项目的年代tart and end are in the same year. If your dates have different years such as Dec. 2019 - March 2020, this will not work.
-
Mike Wilday ✭✭✭✭✭✭
@Paul NewcomeThanks for noticing the missing parenthesis. I edited my original post to include it.
And you're right projects that extend over multiple years would be much more complicated, but I think that it could still be done by comparing the year and month. If you actually used the last date of the month you were checking against you could just compare the dates altogether.
=IF(AND(Date(2019,02,1) >= [Project Start Date]@row, Date(2019,02,27) <= [Project End Date]@row, 1)
@RKAre you looking at a year's work at one time or would this project extend over multiple years? IF my above solution works for you then please mark it as a correct answer.
-
RK ✭
@Mike Wildayand@Paul Newcome, thank you for your quick and elegant responses. I was over-complicating things in my head. Your outside view simplified things greatly.
@Mike WildayProjects will wrap across years, but I think your revised approach solves that for me (I can suffer the once-a-year administrative effort to create new columns).
Thanks to you both!
-
Mike Wilday ✭✭✭✭✭✭
Yes, the start date should be compared to the earliest date in the month. Glad we could help you see a result.
-
Paul Newcome ✭✭✭✭✭✭
No worries. Parenthesis can be a pain sometimes.
You also pulled a "Paul" and replicated my original formula with the only difference being the order you compared the dates in. Hahaha
-
Mike Wilday ✭✭✭✭✭✭
@Paul NewcomeGreat minds think alike. And at close to the same speeds.
-
Paul Newcome ✭✭✭✭✭✭
@Mike WildayI have noticed this. I was just sitting here laughing out loud at that other thread about pulling the most recent updated.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"