Number of NetDays since last event
Hi,
Looking to calculate the number of netdays since an event was recorded.
I have a {Date of Incident} column and a {Classification} column
Looking for the number of days since a specific classification was reported i.e. {Classification}, ="Lost Time Injury".
Want to be able to report this as a Key Indicator of No. Days since Last LTI = ?
Regards
Steve
Comments
-
Paul Newcome ✭✭✭✭✭✭
Try this one...
=IF(Classification@row = "Lost Time Injury", TODAY() - [Date of Incident]@row, "")
-
I get #uparseable.
Also, what do you mean by@row.
I should have mentioned that This is a cross sheet reference I am looking for.
I am looking to put the result on a dashboard.
Regards
Steve
-
Andrée Starå ✭✭✭✭✭✭
Hi Steve,
How did it go? Did you manage to get something set up?
Best,
Andrée Starå - Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
Ah. Ok. X-sheet referencing changes it up a bit. The sheet you are pulling from, I will call "Source Sheet", and the sheet you are putting the formula in , I will call "Master Sheet". You will need to use x-sheet referencing appropriately for this to work.
Source sheet columns used:
Classification
Date of Incident
Master Sheet columns used:
Classification
Net Days Since
Use the following formula in the [Net Days Since] column on your master sheet.
=NETDAYS(INDEX({Source Sheet Range 1}, MATCH(Classification@row, {Source Sheet Range 2}, 0)), TODAY())
Here's the breakdown and how to tweak it so it works for you...
When you get to the part where you need to enter {Source Sheet Range 1}, use the link in the formula helper box the "reference another sheet". Select your source sheet, then select the [Date of Incident] column.
On your master sheet, I assume you will have a column that has listed out the different classifications. That is the Classification column listed under the Master sheet columns used. Classification@row is specifying for the formula to look for whatever data is in the Classification column in whatever row the formula is on. So if you use the exact same formula in multiple rows, each with different classifications, the formula will automatically update itself to reflect whichever classification is on that row without you having to update the formula with each classification.
To get {Source Sheet Range 2}, follow the same steps to reference your source sheet, then select the Classification column.
-
Thanks for that.
What I need is for the number of days since the last event to calculate on the Source Sheet.
I don't want the Net Days Since Column in the Master Sheet as it's a Register. The calculataion needs to sit on another page i.e. "Source Page"
Basically I will have the Source Sheet 2 columns [Net Days Since] and [Classification]. The Classification column on the Source Sheet will have for example:
Row 1 - Lost Time Injury
Row 2 - Medical Treatment Injury
Row 3 - First Aid Injury
The Net Days Since Column will have the days since e.g. a Lost Time Injury event took place from today.
Or,
Could I put another column in [Net Days Since] and hide it from view?
Kind regards
Steve
-
Paul Newcome ✭✭✭✭✭✭
I'm not sure exactly what you're looking for at this point. Net days or no? Cross sheet or no? By cross sheet I mean formula sits on sheet A and pulls info from sheet B. Pulling data for a dashboard isn't necessarily a cross sheet reference. Where exactly is the data being pulled from? Where exactly is the data being displayed?
If you could provide some screenshots of how you want this laid out, that would be great. Just enter some examples and indicate where you want what to go.
-
Hi Paul,
Actually it's worked just fine as it is.
Thanks a great deal
Steve
-
Paul Newcome ✭✭✭✭✭✭
-
I have another problem now.
It won't update unless I open the sheet with the formula in it.
And strangely enough.
The last row only. FAI's.
The top two rows, LTI's and MTI's with the same formula works just fine.
Any idea's?
Regards
Steve
-
Paul Newcome ✭✭✭✭✭✭
当使用今天()函数在一个公式,它will only update when the sheet is opened. Unfortunately there is no way around it that I have found. I wish there was because I have more than a few sheets that I open every single day for ONLY that reason. Everything else is automated.
As far as working on some and not on others... I'd have to see the sheet(s) to see if maybe there is some slight difference somewhere whether it be in format or whatnot.
-
Andrée Starå ✭✭✭✭✭✭
Hi Paul,
A tip.
Link from the sheet or sheets that you use the most to those that should update.
I hope this helps you!
Have a fantastic week!
Best,
Andrée Starå - Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Hi Paul,
Yes I find the same is a bit of an annoyance indeed.
-
The developers need to do a bit of work on the application to do what excel can do if this is to be better than excel.
Excel has a setting to automatically recalculate the sheet even if it contains TODAY()
-
Andrée Starå ✭✭✭✭✭✭
Hi Steve,
I'm confident that the team is working on improving it.
Please submit an Enhancement Request when you have a moment.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
Do you mean to put the TODAY() function on one of my regularly used sheets and simply create a cell link to the not so regularly used sheet, or...?
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":26,"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":25,"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":"