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

Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

Hi Paul,

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!

• ✭✭✭✭✭✭
Options

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.

• Options

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)

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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)

• Options

Hi Paul-

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

• ✭✭✭✭✭✭
Options

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.

• Options

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!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

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?

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!