Track hours for a specific task in a specific month

Hi everyone,

So I am trying to track the hours I spend doing task called e.g. "Test 1" in a given month. I have a separate spreadsheet with Dates (Month, day, year), Duration (in Hours) and column for Category (where I have "Test 1").

你能帮我用一个公式来交易吗ck this?

Thank you!

image.png


Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Try an INDEX\/COLLECT like so:<\/p>

=INDEX(COLLECT({Area Column}, {Plot Column}, @cell = Plot@row, {Valve Number Column}, @cell = [Valve Number]@row), 1)<\/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":108545,"type":"question","name":"If, Index & Collect Formula issues","excerpt":"I have an issue with my current If, Index, and Collect formula. I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I want to automate this process where if I have a TFD C shift, the Reviewers email address will be added into the data collection sheet. Here's my…","snippet":"I have an issue with my current If, Index, and Collect formula. I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I…","categoryID":322,"dateInserted":"2023-08-04T16:47:48+00:00","dateUpdated":null,"dateLastComment":"2023-08-07T12:24:26+00:00","insertUserID":134622,"insertUser":{"userID":134622,"name":"Office of the Medical Director","url":"https:\/\/community.smartsheet.com\/profile\/Office%20of%20the%20Medical%20Director","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T21:09:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T12:49:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":58,"score":null,"hot":3382582334,"url":"https:\/\/community.smartsheet.com\/discussion\/108545\/if-index-collect-formula-issues","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108545\/if-index-collect-formula-issues","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108545,"commentID":389171,"name":"Re: If, Index & Collect Formula issues","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389171#Comment_389171","dateInserted":"2023-08-07T12:24:26+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T12:49:24+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/K7YSB4ZJ9B09\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T21:01:11+00:00","dateAnswered":"2023-08-04T19:31:13+00:00","acceptedAnswers":[{"commentID":389035,"body":"

Try an INDEX\/COLLECT instead.<\/p>

=INDEX(COLLECT({Contact Column}, {Agency Column}, @cell = Agency@row, {Schedule Column}, @cell = Schedule@row), 1)<\/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":108548,"type":"question","name":"Track hours for a specific task in a specific month","excerpt":"Hi everyone, So I am trying to track the hours I spend doing task called e.g. \"Test 1\" in a given month. I have a separate spreadsheet with Dates (Month, day, year), Duration (in Hours) and column for Category (where I have \"Test 1\"). Could you help me with a formula to track this? Thank you!","snippet":"Hi everyone, So I am trying to track the hours I spend doing task called e.g. \"Test 1\" in a given month. I have a separate spreadsheet with Dates (Month, day, year), Duration (in…","categoryID":322,"dateInserted":"2023-08-04T17:11:38+00:00","dateUpdated":null,"dateLastComment":"2023-08-07T12:22:23+00:00","insertUserID":159564,"insertUser":{"userID":159564,"name":"Magda","url":"https:\/\/community.smartsheet.com\/profile\/Magda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WVEnHu-2MXs!uiDntzuzmHs!KYtjodCrvWz","dateLastActive":"2023-08-04T20:22:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T12:49:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":37,"score":null,"hot":3382581841,"url":"https:\/\/community.smartsheet.com\/discussion\/108548\/track-hours-for-a-specific-task-in-a-specific-month","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108548\/track-hours-for-a-specific-task-in-a-specific-month","format":"Rich","lastPost":{"discussionID":108548,"commentID":389169,"name":"Re: Track hours for a specific task in a specific month","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389169#Comment_389169","dateInserted":"2023-08-07T12:22:23+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T12:49:24+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/41LONIWI0UD8\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T20:14:06+00:00","dateAnswered":"2023-08-04T18:53:19+00:00","acceptedAnswers":[{"commentID":389017,"body":"

You would use a SUMIFS.<\/p>

=SUMIFS({Hours}, {Category}, @cell = \"Test 1\", {Dates}, AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2023))<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions