Summarize duration in a project sheet by week/date range?
Is there a way to summarize the DURATION data in a resource-enabled project sheet by week or a given date range? i.e., I want to see total hours for the last week by person. I already have a helper column inserted which converts DURATION to hours.
Thanks!
Answers
-
M Underbrink ✭✭✭✭✭
=SUMIF(AND([DateColumn]@row < TODAY(), [DateColumn]@row TODAY(-7)), [HoursColumnYouWanToSum])
Something similar to this could work. If you wanted to do it for multiple people on a sheet, you'd have to have a rollup/calculation grid, with all of their names on it, but have the sumIF check for the additional condition in which the "Assigned to" column checked if they were that person
=SUMIF(AND([AssignedToColumFromOthersheet]=[AssignedTo]@row, [DateColumn]@row < TODAY(), [DateColumn]@row TODAY(-7)), [HoursColumnYouWanToSum])
Let me know if further clarification is needed!
-
Gordon ✭✭✭✭✭
@M UnderbrinkThank you for the suggestion. Regarding the [Date Column] reference, because these are resource-enabled project sheets, I have two columns: [Start Date] and [End Date].
Using your formula as a starting point, do you think this would work if I made it a SUMIFS instead with an AND for both dates:
=SUMIFS(AND([Start Date]@row < TODAY(), [Start Date]@row
The only problem I keep running into is how to get the [Duration] to be only for the week period if the [Start Date] and [End Date] are longer or shorter than one week. At first I thought I could use NETWORKDAYS, but I couldn't get the formula logic to work based on the [Start Date], [End Date] and last week parameters.
-
M Underbrink ✭✭✭✭✭
Starting to get a little too into specifics for me to be able to solve through a chat forum....
When things start getting too complex, I start to look at why I'm measuring something and if there is an easier way to measure it. If you never want the duration of any given column to count beyond 40 hours for your formula, then I would focus my efforts on restricting the duration column I'm summing to a max of 40 through formulas. Would that work for your purposes? You could have a second column next to Duration that "Max 40 Duration" with the column formula as follows,
=IF([Duration]@row<40, [Duration], 40)
Also, I don't think my solution works as is - it may need some additional tweaking....
Help Article Resources
Categories
=VALUE(LEFT([SubComp#]@row, (LEN([SubComp#]@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":107898,"type":"question","name":"Using MAX to get latest date by criteria","excerpt":"Hello, I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date column. =MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),) Appreciate any suggestions!","snippet":"Hello, I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date…","categoryID":322,"dateInserted":"2023-07-21T11:41:31+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T12:14:07+00:00","insertUserID":135590,"insertUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":135590,"lastUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":33,"score":null,"hot":3379882538,"url":"https:\/\/community.smartsheet.com\/discussion\/107898\/using-max-to-get-latest-date-by-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107898\/using-max-to-get-latest-date-by-criteria","format":"Rich","lastPost":{"discussionID":107898,"commentID":386369,"name":"Re: Using MAX to get latest date by criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386369#Comment_386369","dateInserted":"2023-07-21T12:14:07+00:00","insertUserID":135590,"insertUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+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-07-21T12:14:33+00:00","dateAnswered":"2023-07-21T12:03:58+00:00","acceptedAnswers":[{"commentID":386362,"body":"
In mine the column called Date and the column called Max per Value are BOTH Date data types.<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/LY0NWAX19RK4\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nI have exposed the formula I have used in the last column to show how the Max per Value is coming through.<\/p>
I know this has simplified your original question, but can you see how the Max(Collect()) statement works here?<\/p>
Does this help at all?<\/p>
So in yours: <\/p>
=MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),<\/strong>)<\/p>
Range 6 and the column you have written this formula into must be Date fields and you have an extra comma where you don't need it! Just between the last two )). This comma will cause an error.<\/p>
Kind regards<\/p>
Debbie<\/p>"},{"commentID":386369,"body":"
Hello Debbie,<\/p>
Appreciate your help, that worked perfectly!<\/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":107890,"type":"question","name":"Need Help with Formula to Roll Up and Average Schedule Health.","excerpt":"Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date\/End Date and Task Status. Next, I am trying to roll up\/average those Individual Task schedule health values to an overall Phase schedule health value and then ultimately to the Overall…","snippet":"Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date\/End Date and Task Status. Next, I am…","categoryID":322,"dateInserted":"2023-07-21T00:56:52+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T16:10:36+00:00","insertUserID":163738,"insertUser":{"userID":163738,"name":"BArmendariz","url":"https:\/\/community.smartsheet.com\/profile\/BArmendariz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcJ5xYSxEGHhOSSWfKvut9ttlA26d6GuoryTax_h13HbQ=s96-c","dateLastActive":"2023-07-21T16:10:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163738,"lastUser":{"userID":163738,"name":"BArmendariz","url":"https:\/\/community.smartsheet.com\/profile\/BArmendariz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcJ5xYSxEGHhOSSWfKvut9ttlA26d6GuoryTax_h13HbQ=s96-c","dateLastActive":"2023-07-21T16:10:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":35,"score":null,"hot":3379861048,"url":"https:\/\/community.smartsheet.com\/discussion\/107890\/need-help-with-formula-to-roll-up-and-average-schedule-health","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107890\/need-help-with-formula-to-roll-up-and-average-schedule-health","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107890,"commentID":386456,"name":"Re: Need Help with Formula to Roll Up and Average Schedule Health.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386456#Comment_386456","dateInserted":"2023-07-21T16:10:36+00:00","insertUserID":163738,"insertUser":{"userID":163738,"name":"BArmendariz","url":"https:\/\/community.smartsheet.com\/profile\/BArmendariz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcJ5xYSxEGHhOSSWfKvut9ttlA26d6GuoryTax_h13HbQ=s96-c","dateLastActive":"2023-07-21T16:10:48+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-07-21T16:11:26+00:00","dateAnswered":"2023-07-21T12:51:20+00:00","acceptedAnswers":[{"commentID":386383,"body":"
@BArmendariz<\/strong><\/a><\/p>
The first half of this is dealing with Parent Rows:<\/p>
