Counting hours against a target (14)

[nikolacm]

Hi there I am trying to calculate the total hours per date of an intuition program that I attend. First column shows the date, second column the hours the lesson took place and third column shows the cumulative hours. When I reach 14 hours I have to pay the agreed fee. To this end, I am looking for a formula which will restart counting from zero when I reach 14 hours.
I have tried a Sum - offset formula along with If statement but I am stuck. Any ideas ?


Thank you in advance,

Book1
	B	C	D	E
5	Agreed Hours	14
6
7		Date	Hours	Cumulative Hours
8		10-01-2022	2	2
9		11-01-2022	2	4
10		12-01-2022	2	6
11		13-01-2022	2	8
12		14-01-2022	2	10
13		15-01-2022	2	12
14		16-01-2022	2	14
Sheet1

Cell Formulas
Range	Formula
E8:E14	E8	=SUM(D$8:D8)

 

[6StringJazzer]

Update your formula in row 8 to:

=MOD(SUM(D$8:D8)-1,14)+1

and fill down

 

[BSALV]

Map1
	B	C	D	E	F
5	Agreed Hours	14
6
7		Date	Hours	Cumulative Hours	payment
8		44571	2	2
9		44572	2	4	0
10		44573	2	6	0
11		44574	2	8	0
12		44575	2	10	0
13		44576	2	12	0
14		44577	2	0	1
Blad1

Cell Formulas
Range	Formula
E8:E14	E8	=MOD(SUM(D$8:D8),$C$5)
F9:F14	F9	=TRUNC((E8+D9)/$C$5)

 

[nikolacm]

Update your formula in row 8 to:

=MOD(SUM(D$8:D8)-1,14)+1

and fill down

Many thanks for your assistance. I was expecting it more complex but MOD seems to be a great function for that case!!

 

[nikolacm]

Map1
	B	C	D	E	F
5	Agreed Hours	14
6
7		Date	Hours	Cumulative Hours	payment
8		44571	2	2
9		44572	2	4	0
10		44573	2	6	0
11		44574	2	8	0
12		44575	2	10	0
13		44576	2	12	0
14		44577	2	0	1
Blad1

Cell Formulas
Range	Formula
E8:E14	E8	=MOD(SUM(D$8:D8),$C$5)
F9:F14	F9	=TRUNC((E8+D9)/$C$5)

Many thanks !

 

[6StringJazzer]

Oh, and you can replace 14 in the formula with $C$6 since it's in your sheet.

 

[6StringJazzer]

=MOD(SUM(D$8:D14),$C$5)

This formula will never show 14 (see row 14 in OP)