sum a quarter of data but change calc mid quarter

[dmheller]

So i have a calculation that happens it is in cell G58 and is D53*D58 D53 is hours
What happens is, D58 is based on test results so i have a sheet "testing" and in cell c4 is the first test result then cell c5 and so on down the line and then there is a date in B4 and B5 and so on down the line in pounds per hr.
What i do is sum up a quarters worth of data but if the test results change in that quarter, the calculation doesn't account for it. Does anyone know a way to do multiple math problems in the same equation but use the correct numbers? Here is an example where the 2nd quarter result should be (.2*50(number of days before test result in q2)*24+.6*51(number of days after test result in q2)*24
the result in 974.4
It got complicated for me when i started indexing the dates then using the eomonth and trying to count days. If anyone can help that would be great. I really just need the first equation then i can copy for all quarters. One thing, a test might not happen in a quarter so q3 would just be days*hrs*.6 because no test happened. I can try to send sheets over but really, there isnt much to show, just better explain if you need it. thanks in advance.

date	result
2/20/2024	0.2
5/20/2024	0.6

 

[Sam_D_Ben]

Please check,

Book1
	A	B
1	Quarter Start Date	Quarter End Date
2	1-Apr	30-Jun
Sheet2

Book1
	B	C
4	02/20/24	0.2
5	05/20/24	0.6
Testing

Book1
	D
58	974.4
Sheet2

Cell Formulas
Range	Formula
D58	D58	=(Testing!C4)*((MIN(B2,Testing!B5)-A2+1)*24)+((Testing!C5)*(MAX(A2,Testing!B5+1)-A2+1)*24)

 

[dmheller]

Please check,

Book1
	A	B
1	Quarter Start Date	Quarter End Date
2	1-Apr	30-Jun
Sheet2

Book1
	B	C
4	02/20/24	0.2
5	05/20/24	0.6
Testing

Book1
	D
58	974.4
Sheet2

Cell Formulas
Range	Formula
D58	D58	=(Testing!C4)*((MIN(B2,Testing!B5)-A2+1)*24)+((Testing!C5)*(MAX(A2,Testing!B5+1)-A2+1)*24)

Thanks but my issue is this will change quarterly and i keep a running long. I guess i could just have 2 data points in there and keep the running log elsewhere.
I had the quarter date in H6 but my equation was:
INDEX(b4:c5,MATCH(H6,b4:b5,1),2)*(INDEX(b4:c5,MATCH(EOMONTH(H6,2),b4:b5,1),1)-H6)+(INDEX(b4:c5,MATCH(EOMONTH(H6,2),b4:b5,1),2)*((EOMONTH(H6,2)+1)-(INDEX(b4:c5,MATCH(EOMONTH(H6,2),b4:b5,1),1))))
this was to allow for the running log but i am not sure it works. It does reference the testing sheet because i just wrote it on that sheet

 

[Sam_D_Ben]

How about this,

=(INDEX(C4:C5,MATCH(H6,B4:B5,1)))*((MIN(EOMONTH(H6,2), INDEX(B4:B5,MATCH(EOMONTH(H6,2),B4:B5,1)))-H6+1)*24)+((INDEX(C4:C5,MATCH(EOMONTH(H6,2),B4:B5,1)))*(MAX(H6,INDEX(B4:B5,MATCH(EOMONTH(H6,2),B4:B5,1))+1)-H6+1)*24)