Need Help Sorting & Grouping "Day" Data By "Week"

Casey Jones

I'm brand new to functions and Smartsheets in general so please bear with me as I attempt to learn with you all. I've read over the functions documentation and to be honest, it's a little over my head. I'm trying to suss out a step by step otherwise I get lost. But I'm hoping I'll get there eventually!

Here's the nuts of it.

I'm trying to take day by day data for each employee, group and total each employees hours by week, multiply each week by our employee rate card, total each week, and then extrapolate that data to various other sheets. I'm searching for one source of truth here.

Our timesheet software exports data by a list of Name/Date/# of Hours

• Beth - 1/1/22 - 5
• Beth - 1/2/22 - 4
• Beth - 1/3/22 - 3
• Charles - 1/2/22 - 1
• Charles 1/7/22 - 4
• Seth 1/2/22 - 3
• Seth 1/14/22 - 3

Here's what I'm hoping to accomplish.

Beth is $25 an hour

Charles $10 an hour

Set $5 an hour

Week 1

Beth's week 1 hours (12) X Rate card ($25) = $300

Charles week 1 hours (1) X Rate Card ($10) = $10

Seth week 1 hours (3) X Rate Card ($5) = $15

Week 1 = $325 (sent to project overview sheet)

Week 2

Beths week 2 hours (0) X Rate Card = $0

Charles week 2 hours (4) X Rate Card ($10) = $40

Seth Week 2 hours (0) X Rate Card = $0

Week 2 = $40 (sent to project overview sheet)

Week 3

Beth's week 3 hours (0) X Rate card ($25) = $0

Charles week 3 hours (0) X Rate Card ($10) = $0

Seth week 3 hours (3) X Rate Card ($5) = $15

Week 3 = $15 (sent to project overview sheet)

My final overview sheet would show

Week 1 = $325 - Week 2 = $40 - Week 3 = $15

Project Total = $380 (I'd then take this total and send it out to a "all project sheet")

I've figured out how to multiply the rate card by total number of hours. I Just don't know how to get the "total number of hours" "per employee" by "week" without a whole bunch of manual work. The goal would be to simply set it up so I can paste the list large list of Name/Date/Hours data and it automatically calculates the totals by week.

Dale Murphy

@Casey Jones If the data can be parsed so that you have a true date in a date column, you should be able to use the WEEKNUMBER function to produce a column of Weeks that can then be used to sum records on a week by week basis.

You will probably end up with at least one other sheet to be used to do the calculations so that the raw data is kept separate. The raw data sheet would have your new timesheet data rows added to the bottom every period, and extra columns that "calculate" the week of the row. Your calculator sheet would have a list of staff and then reference the timesheet data.

Make any sense?

dm