Subtract dollar amount only after a certain date has passed

[BiblioManiac]

I have inherited a spreadsheet that is used to calculate balances for in incentive program for nurses in my organization.

Nurses submit hours and earn $1.25 per hour. If they spend the amount they accumulated, that is recorded in the spreadsheet and that amount is substracted from their balances.

The organization recently enacted a two year expiration for these hours that is based on the month. So hours recorded in March 2020 would expire April 1, 2022. Hours recorded April 2020 would expire May 1, 2022 and so on.

The hours are also not supposed to expire if the nurse has spent part of the balance.

I almost have a working formula for this, but I cannot get the date aspect to work properly.

It is still subtracting from the balance even if the hours were entered in a month still within the 2 year timeframe.

Any assistance is greatly appreciated!

Example Spreadsheet1-5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	ID		Forwarded	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Totals
2	10419309	PIP Hrs				84.0	84.0	36.0									36.0	51.0	12.0		12.0		48.0	24.0	48.0	36.0		471.00
3		$ Spent																$198.27						$168.09				$366.36
4		$ Earned	$0.00	$0.00	$0.00	$105.00	$210.00	$255.00	$255.00	$255.00	$255.00	$255.00	$255.00	$255.00	$255.00	$255.00	$300.00	$165.48	$168.09	$168.09	$168.09	$168.09	$168.09	$0.00	$0.00	$0.00	$0.00	$0.00
5		Expired		$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$12.39	$0.00	$15.00	$0.00	$60.00	$30.00	$60.00	$45.00	$0.00	24
Sheet1

Cell Formulas
Range	Formula
AB2:AB3	AB2	=SUM(D2:AA2)
R4:AA4,D4:P4	D4	=MAX(C4+(D2*1.25)-D3-D5, 0)
Q4	Q4	=MAX(P4+(Q2*1.25)-Q3-Q6, 0)
AB4	AB4	=+AA4
D5	D5	=MAX(0,SUMIFS($D$2:D$2,$D$1:D$1,"<"&EDATE(D$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:C$5))
E5	E5	=MAX(0,SUMIFS($D$2:E$2,$D$1:E$1,"<"&EDATE(E$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:D$5))
F5	F5	=MAX(0,SUMIFS($D$2:F$2,$D$1:F$1,"<"&EDATE(F$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:E$5))
G5	G5	=MAX(0,SUMIFS($D$2:G$2,$D$1:G$1,"<"&EDATE(G$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:F$5))
H5	H5	=MAX(0,SUMIFS($D$2:H$2,$D$1:H$1,"<"&EDATE(H$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:G$5))
I5	I5	=MAX(0,SUMIFS($D$2:I$2,$D$1:I$1,"<"&EDATE(I$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:H$5))
J5	J5	=MAX(0,SUMIFS($D$2:J$2,$D$1:J$1,"<"&EDATE(J$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:I$5))
K5	K5	=MAX(0,SUMIFS($D$2:K$2,$D$1:K$1,"<"&EDATE(K$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:J$5))
L5	L5	=MAX(0,SUMIFS($D$2:L$2,$D$1:L$1,"<"&EDATE(L$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:K$5))
M5	M5	=MAX(0,SUMIFS($D$2:M$2,$D$1:M$1,"<"&EDATE(M$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:L$5))
N5	N5	=MAX(0,SUMIFS($D$2:N$2,$D$1:N$1,"<"&EDATE(N$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:M$5))
O5	O5	=MAX(0,SUMIFS($D$2:O$2,$D$1:O$1,"<"&EDATE(O$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:N$5))
P5	P5	=MAX(0,SUMIFS($D$2:P$2,$D$1:P$1,"<"&EDATE(P$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:O$5))
Q5	Q5	=MAX(0,SUMIFS($D$2:Q$2,$D$1:Q$1,"<"&EDATE(Q$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:P$5))
R5	R5	=MAX(0,SUMIFS($D$2:R$2,$D$1:R$1,"<"&EDATE(R$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:Q$5))
S5	S5	=MAX(0,SUMIFS($D$2:S$2,$D$1:S$1,"<"&EDATE(S$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:R$5))
T5	T5	=MAX(0,SUMIFS($D$2:T$2,$D$1:T$1,"<"&EDATE(T$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:S$5))
U5	U5	=MAX(0,SUMIFS($D$2:U$2,$D$1:U$1,"<"&EDATE(U$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:T$5))
V5	V5	=MAX(0,SUMIFS($D$2:V$2,$D$1:V$1,"<"&EDATE(V$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:U$5))
W5	W5	=MAX(0,SUMIFS($D$2:W$2,$D$1:W$1,"<"&EDATE(W$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:V$5))
X5	X5	=MAX(0,SUMIFS($D$2:X$2,$D$1:X$1,"<"&EDATE(X$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:W$5))
Y5	Y5	=MAX(0,SUMIFS($D$2:Y$2,$D$1:Y$1,"<"&EDATE(Y$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:X$5))
Z5	Z5	=MAX(0,SUMIFS($D$2:Z$2,$D$1:Z$1,"<"&EDATE(Z$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:Y$5))
AA5	AA5	=MAX(0,SUMIFS($D$2:AA$2,$D$1:AA$1,"<"&EDATE(AA$1,AB5))*1.25-SUM($AB$3)-SUM($C$5:Z$5))

 

[Evil Bean Counter]

I went down a different road.

I create a data table

In the exclude column I created a formula to ignore earnings more than 24 months old unless some of those earnings had been spent (which I think it what you specified)

Then I created a pivot table, using the Include field as a slicer, January 2020 is excluded from the table because its more than 24 months old and there was no partial spending of the hours earned.

Perhaps this gives you another solution.