Summarizing Values In Last 15 Work Days

Hi all!

I would like to create a report that has a list of names, as well as the hours logged in the last 15 working days (weekdays). Currently, we have a sheet that has all accomplished tasks inside of it, attached to those tasks is the name of the person assigned to it, as well as the hours they committed to it and the day it was finished. What I would like to do is create a report that tracks the total amount of hours spent on any project in the last 15 working days. So I would like for the report to look something like this

Currently (in our sheet), the logs of tasks/project are defined row-wise by the project name. The individual tasked with it is selected through a drop down in a column along this row. So it would be something like:

col1: col2: col3: col4:

Row1: project 1 - tasked person (dropdown) - date finished - hours committed

Row2: project 2 - tasked person (dropdown) - date finished - hours committed

What I want in the report is something like the attached photo:

col1: col2:

Row1: Tasked Person 1 x

Row2: Tasked Person 2 y

Row3: Tasked Person 3 z

Where each row will have the tasked person's name, and the second column will have the total hours committed within the last 15 working days. I hope this explanation makes sense, please feel free to let me know if it doesn't I'll do my best to clarify! Thank you all so much, I really appreciate it, I have been having trouble wrapping my head around how to get this done for awhile now, and I've come up on a blank.

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

If I understand you correctly.... You would create a new sheet, then set up your cross-sheet references to point to the columns you need. This formula should then get you the information you want, with some slight changes to column names to fit your situation. A couple of things to note: TODAY(-15) will include today in the fifteen days, so if you want to go back a full fifteen days in the past, you would use TODAY(-16). I wasn't sure how you wanted to treat it. Also, this will give you hours for tasked COMPLETED within the last 15 days. If these tasks span multiple days, it may not be entirely accurate as to the number of hours worked.

=SUMIFS({Hours Committed}, {Tasked Person}, Person@row, {Date Finished}, @cell > TODAY(-15))

Muhammad

https://community.smartsheet.com/discussion/comment/419803#Comment_419803

Hi Carson! Sorry for the bother here, I am trying to implement the formula you mentioned above and I am having a little difficulty. I was wondering if you could help walk me through what the variables of the formula mean here. So for more clarity, I'll detail the sheet I am reference more just below:

We have a sheet called "Custom Engineering Requests", and in this sheet we keep all the logged requests and their details. A typical line will include something like this:

col1 col2 col3 col4 col5 coln

Row1: "Request number" "Request Description" "Assigned To" "Eng1 Hours" "Eng 2 Hours" ... "date completed"

There is also a "Total Hours" Column. I would like to make a new sheet where every engineers hours can be broken down into "total hours in the last 15 working days (this must exclude Saturdays and Sundays). I hope this makes things more clear if they were not already, and I have just added more detail so the breakdown of the formula will be more comprehensive for myself. Thank you again for your initial response, this has helped tons!

Carson Penticuff

Is it possible for you to share a screenshot of what your Custom Engineering Requests sheet looks like? A mock-up version is fine if the information is confidential. Based on your updated description, the formula I provided would likely need some tweaking to work and I want to make sure I understand your situation.

Muhammad

https://community.smartsheet.com/discussion/comment/420254#Comment_420254

Sure no problem! I had to make a sort of mockup as there are dozens more columns in the actual sheet that are just not relevant to this problem (and as you mentioned, some confidential information), but this is the gist of it (description and request # are not super relevant either, but I think they help with context a little). So basically, imagine a sheet filled with thousands of entries like below, each row is a task, every task is assigned to someone at some point, and then completed at some point. What I am trying to get out of the formula is to summarize an Engineers logged hours is the last 15 working days (this means excluding weekends). So that is the say for example, if I want the last 7 working days from today Feb 20th 2024, the days I am looking to capture are not the actual last 7 days, but rather Feb 20th[Tue], Feb 19[Mon], Feb 16[Fri] (note we skipped the weekend here), Feb 15[Thur], Feb 14[Wed], Feb 13[Tue], and Feb 12[Mon]. The first Image below is the mockup of our Custom Engineering Requests Sheet, the image below that is a mockup of the sheet I am trying to make.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.