How to display Days Elapsed only if Status is Open?
Like the title says, I'd like to only show the number of days elapsed when the status is on Open. I'm currently using this formula:
=IFERROR(NETWORKDAYS([Report Date]@row, [Due Date]@row), "")
Any tips would be highly appreciated!
Answers
-
PJ Beaudry ✭✭In your current scheme, think it would be something like:
=IFERROR(IF(Status@row="Open",NETWORKDAYS([Report Date]@row, [Due Date]@row),""),"")
However, the formula above would only seem to change if the "Report Date" is updated, and then only to show how long over or under the "Due Date" that "Report Date" is, which would be more like "Days to Due Date".
Assuming the "Report Date" is the start of the item, if you're looking to calculate "Days Elapsed" from the "Report Date" to the current day and then stop that calculation when the item is "Resolved", you may want to
- Add a "Resolved Date" column
- Write a workflow triggered "when rows are changed" When "Status" changes to "Closed", with the action as "Record a date" in the "Resolved Date" column
- Write the following formula in the "Days Elapsed" column:=IF(ISBLANK([Resolved Date]@row), NETWORKDAYS([Report Date]@row, TODAY()), NETWORKDAYS([Report Date]@row, [Resolved Date]@row))
-
afelo ✭The top formula did exactly what I wanted, thank you! Just wondering if it's possible to leave it blank instead of a dash. But its not a big deal.
I'll create the Resolved column and see if that bottom formula works better for us in the long run.
Thanks again!
-
PJ Beaudry ✭✭
Excellent!
Sounds good! I made some tweaks to the process and formula in an edit after additional thought... definitely reference those if needed.
Help Article Resources
Categories
- The cell is a multicontact cell and contains a match for the contact<\/li>
- The cell is a multiselect dropdown and contains a match<\/li>
- The cell is not a multicontact or multiselect dropdown and contains ONLY and EXACTLY the text you are matching against. <\/li><\/ol>
I am assuming your row in question is a standard Text\/Number cell? If so, you will need to get somewhat creative with some CONTAINS() statements.<\/p>
This will narrow down your matches to only cells that contain a space both before and after TAC. If, however, TAC can appear as the first or last \"word\" in the cell, it would not match in that circumstance as there would only be a space on one side.<\/p>
=IF(CONTAINS(\" TAC \", [Column Name1]@row), 1, 0)<\/p>
<\/p>If TAC can appear as the first or last \"word\" in the cell, this will cover those possibilities as well.<\/p>
=IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\"), 1, 0)<\/p>
<\/p>If it is possible that TAC will be the exact entry of the cell, you will need an additional statement to cover that, as there would not be spaces on either side. This will add that option.<\/p>
=IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\", [Column Name1]@row = \"TAC\"), 1, 0)<\/p>
<\/p>If there are other situations you may have in your sheet, i.e., TAC inside parenthesis, or before or after punctuation, etc, you will additional statements to include those options as well.<\/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":109170,"type":"question","name":"COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","excerpt":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a \"High\", \"Medium\", or \"Low\" value based on the calculated score in column…","snippet":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another…","categoryID":322,"dateInserted":"2023-08-18T20:29:26+00:00","dateUpdated":null,"dateLastComment":"2023-08-18T21:13:12+00:00","insertUserID":165367,"insertUser":{"userID":165367,"name":"Shari D","url":"https:\/\/community.smartsheet.com\/profile\/Shari%20D","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T21:12:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":91,"score":null,"hot":3384789758,"url":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","format":"Rich","lastPost":{"discussionID":109170,"commentID":391505,"name":"Re: COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391505#Comment_391505","dateInserted":"2023-08-18T21:13:12+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+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-18T21:12:26+00:00","dateAnswered":"2023-08-18T21:01:48+00:00","acceptedAnswers":[{"commentID":391499,"body":"
