date/ number of days in range formula help

[Flora2021]

Hi, I have a spreasheet template where I enter a number of billing days. I then have to calculate number of days in each date range based on that number.
For example, If the total number of billing days is 123 days, I have to add how many days it was in the 71-90 range, then the 91-120 range and so on. We only start charging from days 71-90.
I have to use this because we have to charge different amounts based on the accumulating date ranges. We would only charge say 60 per day for range 91-120 and not from days 71-90. I can figure out the calculation of dollars but I am not sure how to get the count of days per each range. Thanks for you help in advance.

 

[Dave Patton]

T202304a.xlsm
	A	B	C	D	E	F
1			1-70	71-90	91-120	121-x
2		123	70	20	30	3
3
1d

Cell Formulas
Range	Formula
C2	C2	=(B2>0)*MIN(B2,70)
D2	D2	=(B2>70)*MIN(B2-70,20)
E2	E2	=(B2>90)*MIN(B2-90,30)
F2	F2	=(B2>120)*(B2-120)

 

[Flora2021]

Thank you so much!!! I really appreciate your help!

 

[Dave Patton]

Thanks for the feedback.

 

[Dave Patton]

You could complete the charge calculation with one formula.
I included an example with a table and another that names the bracket and rate differential information.
Edit the information for your actual rates.

T202304a.xlsm
	A	B	C	D
1		Number	Charge	Charge
2		123	3,010.00	3,010.00
3		50	0.00	0.00
4		200	8,400.00	8,400.00
5
1d

Cell Formulas
Range	Formula
C2:C4	C2	=SUM((B2>$B$16:$B$19)*(B2-$B$16:$B$19)*($C$16:$C$19-$C$15:$C$18))
D2:D4	D2	=SUM((B2>aB)*(B2-aB)*aR)

The formula in D2 uses named information; use Name Manager .
name the brackets information aB specify value ={0;70;90;120}
name the relevant Rate Differential aR ={0;50;10;10}

T202304a.xlsm
	A	B	C	D	E	F	G
6
7
8		Number	1-70	71-90	91-120	>=121
9		123	70	20	30	3
10				50	60	70
11				1000	1800	210
12
13
14		Brackets	Rates
15
16		0	0
17		70	50
18		90	60
19		120	70
1d

Cell Formulas
Range	Formula
C9	C9	=(B9>0)*MIN(B9,70)
D9	D9	=(B9>70)*MIN(B9-70,20)
E9	E9	=(B9>90)*MIN(B9-90,30)
F9	F9	=(B9>120)*(B9-120)
D11:F11	D11	=D9*D10