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.
