Total dollar amount if hours are over 40.

[mcjohnson3211]

Thanks in advance. I'm running into another formula I can't figure out through my research.

I'm doing timekeeping using Excel. And I need only to total the amounts that are over 40 hours. But the rates of each role are different, so once the total of a team goes over 40 hours, I need to total the cost of hours for everything over 40. I hope that makes sense.

Team Role	Date	Hours	Hour Fee
Solution Manager	9/1/2023​	0.5​	$ 100.00			Role Hourly Rate	Hourly Rate	Monthly Total Hours
Solution Architect	9/1/2023​	1​	$ 200.00			Account Manager	$ 150	0​
Delivery Manager	9/1/2023​	0.25​	$ 37.50			Account Director	$ 185	0​
Solution Architect	9/4/2023​	0.5​	$ 100.00			Solution Architect	$ 200	5​
Solution Architect	9/4/2023​	1.5​	$ 300.00			Solution Manager	$ 200	2​
Solution Architect	9/4/2023​	0.5​	$ 100.00			Database Developer	$ 225	0​
Delivery Manager	9/5/2023​	0.5​	$ 75.00			Software Engineer	$ 225	0​
Delivery Manager	9/5/2023​	1.5​	$ 225.00			Campaign Developer	$ 125	0.08​
Solution Manager	9/6/2023​	1​	$ 200.00			Deliverability Specialist	$ 175	0​
Delivery Manager	9/6/2023​	1.25​	$ 187.50			Analytics Manager	$ 175	0​
Delivery Manager	9/6/2023​	0.5​	$ 75.00			Strategy Manager	$ 175	0​
Data Services	9/6/2023​	0.58​	$ 101.50			Delivery Manager	$ 150	9.25​
Campaign Developer	9/6/2023​	0.08​	$ 10.00			Data Services	$ 175	1.33​
Solution Manager	9/7/2023​	0.5​	$ 100.00
Solution Architect	9/7/2023​	1​	$ 200.00			Total for Highlighted in blue		16.25​
Solution Architect	9/7/2023​	0.5​	$ 100.00			Total if over 40 hours		-23.75​
Delivery Manager	9/7/2023​	1.5​	$ 225.00			Cost if over 40 hours
Delivery Manager	9/7/2023​	0.5​	$ 75.00
Data Services	9/7/2023​	0.17​	$ 29.75
Delivery Manager	9/8/2023​	0.75​	$ 112.50
Delivery Manager	9/8/2023​	1.5​	$ 225.00
Data Services	9/8/2023​	0.58​	$ 101.50
Delivery Manager	9/13/2023​	0.5​	$ 75.00
Delivery Manager	9/13/2023​	0.5​	$ 75.00

 

[mcjohnson3211]

So, who's 8 hours is the overtime? Or do you want a weighted average?

That's hard, and it would need to be anyone who logs time after we get the 40 hours. So with the example, as of 09/15, there were 19 hours. But only 17 hours needed to go to the 40. Everything after would be overtime. Because the team is one bucket, it doesn't matter who uses the 40 hours.

 

[awoohaw]

That's hard, and it would need to be anyone who logs time after we get the 40 hours. So with the example, as of 09/15, there were 19 hours. But only 17 hours needed to go to the 40. Everything after would be overtime. Because the team is one bucket, it doesn't matter who uses the 40 hours.

Well, if that is important; and you don't record the person that went over the threshold and all the following people after that then how can you expect a solution with what you have.