How to estimate hours per Task using % allocation, Assigned to and Duration Columns?
Hi Everyone,
I hope you all are doing alright.
We need to estimate how many hours will problaby be spent on each task. We already use %allocation, Assigned to and Duration columns in our cronograms. We can't use the suggested formula "%allocation * Duration * 8" because:
1) Multiple employees can be assigned on the same task. I have no idea how Smartsheet can "break" the cell to read which employees are assigned to the task.
2) Not all of our employees are full-time, we also have part-time and interns assigned in our projects, so the number of hours worked per day may vary according to the Employee. We already have a sheet that contains the employees names and hours worked per day.
Do you know how can I calculate it using Smartsheet?
Thank you and have a great week!
Best Answers
-
Paul Newcome ✭✭✭✭✭✭
To do this with multiple contacts, you would need to parse out each contact into its own column across the row. Then you would use an INDEX/MATCH for each of the individual columns to pull the hours.
-
Paul Newcome ✭✭✭✭✭✭
HEREis a link to a parsing solution that pulls data from a list across a row into multiple columns based on a specific delimiter.
To modify the linked solution, the JOIN column would be your multi-contact column, and you would replace all instances of
"/"
with
CHAR(10)
Answers
-
Mark Cronk ✭✭✭✭✭✭
Can you share some screetshots of your sheet columns to help us understand what you're looking for?
Thank you,
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi@Mark Cronk,
Thank you for the response.
Sure! As seen below, we have "Duration", "% Allocation" and "Employee" columns.
On another sheet, we have the amount of working hours per day per employee.
So basically we want to create an Column called "Hours" and estimate how many hours will problably be spent on each task. For example, if we want to estimate how many hours will be spent on the task below, the calculation would be:
Duration: 5 days / Allocation: 10% / Employee: Gerente and Pedro Concílio / Hours worked per day: Gerente - 8,5 hours; Pedro Concílio - 6 hours
Hours = 5*0,1*8,5 + 5*0,1*6 = 7,25 hours.
This column would be really important to us. I hope that you can help!
Thank you very much and have a great week!
-
Mark Cronk ✭✭✭✭✭✭
你好,佩德罗,
This is a real challenge. You're asking to parse out values from a multi-select contact list and then look up values for each in order to do other math. I need to give it some thought. There are lots of smart people in the Community. Someone else may offer a solution before me.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thank you for the response, Mark!
Do you know how we could do it if we ensure that the Employee column will be filled with one contact only? We are considering to change the column settings to make this calculation easier.
I was thinking about using a formula similar to Excel "=index(...,(match..))" to extract the "Hours worked per day" from the other sheet, so it would "read" the Employee name on the main sheet and get the Hours Amount from the other one. Unfortunately I don't know how to do it in Smartsheet.
Have a great day!
-
Mark Cronk ✭✭✭✭✭✭
你好,佩德罗,
With a single contact it would work just like Excel. You'd do an Index-Match or Vlookup to find the hours worked by the employee and pull that into your formula. There has to be a way to do it with multiple contacts though. Someone will have an answer.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Paul Newcome ✭✭✭✭✭✭
To do this with multiple contacts, you would need to parse out each contact into its own column across the row. Then you would use an INDEX/MATCH for each of the individual columns to pull the hours.
-
Paul Newcome ✭✭✭✭✭✭
HEREis a link to a parsing solution that pulls data from a list across a row into multiple columns based on a specific delimiter.
To modify the linked solution, the JOIN column would be your multi-contact column, and you would replace all instances of
"/"
with
CHAR(10)
-
Paul Newcome ✭✭✭✭✭✭
NOTE: The above modification is inaccurate. CHAR(10) is not the delimiter in a multi-select contact column. I will need to do some digging to try to find out what the delimiter actually is.
EDIT: Even though it is not visible, the delimiter is ", " (comma space)
-
It worked!!
Thank you very much for your help, it will be very useful for our company.
Have a great day!
-
Paul Newcome ✭✭✭✭✭✭
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":"