Sum of hours worked by employee for current week and previous week using weeknumber

Scott Prater

Hi All,

I am spinning my wheels trying to get this formula figured out. Caveat is I'm a fairly new Smartsheet user.

Essentially I am using a request tracker sheet to document and drive a dash where I want to show the amount of time each employee spent per task week over week. I'm using week numbers and on my metrics sheet I am trying to write a formula to show this. Screen grabs show my sheets and current formula, which returns zero.

Goals:

Sum hours worked by person for current week.

Sum hours worked by person previous week.

Create line chart in dash that shows week over week by person.

I'm not sure how to go about this and correct the formula I have. Maybe my approach is all wrong too. Any help and advice would be greatly appreciated!

Thanks,

Scott

Paul Newcome

You need to manually enter the week numbers there in row 1 of the metrics sheet.

So in [Week 40]1 you would manually enter 40.

Paul Newcome

What are the formulas in your Week and Hours Worked columns?

Scott Prater

Hi Paul,

Thanks for the reply!

Week column: =WEEKNUMBER([Actual Completion Date]@row)

Hours Worked column: to be input by staff

Goal is to get all of my metrics to run by current week number, but can't seem to figure out how to effectively write this formula. The only thing I want to track week over week is hours worked by staff. Everything else current week as mentioned.

I think the invalid data types are likely a factor too.

Also want to be able to incorporate weeknumber into this metric below.

Thanks again!

Paul Newcome

You are definitely going to need to take care of the Invalid Data Type errors first as that error will pull through into any formula that is referencing it even if the formula is fine.

Scott Prater

Agreed- was getting an error because I had blanks in the Actual Completion Date column. Corrected and now I have week numbers and no error in entire column.

=WEEKNUMBER([Actual Completion Date]@row)

Paul Newcome

Ok. Next step is to create a Metrics sheet. In a column other than the Primary column (but still to the left of everything else), make it a contact column and enter everyone's names starting in row 2. Then going off to the right, create a column for each week number. Across row 1, manually enter each week number.

Then in row 2 of the first week column (following appropriate steps to reference another sheet) you are going to want to create a formula that looks like this:

=SUMIFS({Reference Sheet Hours Worked Column}, {Reference Sheet Week Number Column}, [Week 1]$1, {Reference Sheet Contact Column}, $[Metrics Sheet Contact Column]@row)

Then you can dragfill this down and over to populate the rest of the table.

Scott Prater

Thank you, Paul! Feel like I'm getting closer. Really appreciate all of the help- you are a wealth of knowledge...

Paul Newcome

For the reference to the week number column, you have "[Week 1]" in the formula, but you do not have a "Week 1" column on the sheet. Additionally, that final cell reference should only be "$Owner@row"

=SUMIFS(Other Sheet Range 1}, {Other Sheet Range 2}, [Week 40]$1, {Other Sheet Range 3}, $Owner@row)

Scott Prater

Hi Paul-

Amazing! Feel like I'm really close to making this work...

Paul Newcome

You need to manually enter the week numbers there in row 1 of the metrics sheet.

So in [Week 40]1 you would manually enter 40.

Scott Prater

WORKED. You made my week! Can't thank you enough.

I have a meeting set up with the Smartsheet Pro Desk on Monday. The more I learn about Smartsheet, the more I recognize the utility in it.

Thanks again!

Paul Newcome

Happy to help. 👍️

bradleyesmith86431

This is a great thread! I am trying to just create a current hours worked summary for my team. They fill out a form/time entry each time that goes into a Master Sheet. I am trying to just summarize the current week hours worked. I did create a column called 'Week Number" and will just hide it. However, I cannot find a simple summary formula that works to simply Sum if the hours are in the current week? Any formula ideas for this example?

Andrée Starå

Hi @bradleyesmith86431

I hope you're well and safe!

Have you explored using a report instead combined with the grouping feature?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!