Changing a value based on data in a previous cell/previous tab - looking back 180 days

[PinkyBoco]

I am trying to set up a spreadsheet for attendance points. I have each employee on a row and the columns are headed with the days/dates of the month. There is a single month per worksheet. I will need the formula to look back 180 days to see if there is a point value on that date, and if so, that value needs to be subtracted from the current date's total points. Is this even possible? I am not an excel master by any means, but have gotten the worksheet built successfully so far!

 

[felixstraube]

How about something like this?:
All in one sheet. Columns go till end of 2023.

Here you can download the workbook:

Attendance points.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	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT
1
2		Month	ene-23
3									ene																															feb																												mar
4				1	0,25	0,5	2	0,25	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi
5			Total	A	TE<	TE>	N	P	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
6		Employee	6	1	0,25	0,5	4	0,25		P			TE>						TE<						N							A						N
7		John Connor	4,25	2	0,25	-	2	-				N												A			A						TE<								A
8		Walter White	-	-	-	-	-	-																																		TE<																	TE>	TE>	TE>
9		Clark Kent	-	-	-	-	-	-																																					N	N	N	P	P
Hoja1

Cell Formulas
Range	Formula
I3:CT3	I3	=IF(DAY(I5)=1,TEXT(I5,"mmm"),"")
I4:CT4	I4	=TEXT(I5,"ddd")
C6:C9	C6	=SUM(D6:H6)
D6:E6	D6	=SUM((YEAR($I$5:$NI$5)=YEAR($C$2))*(MONTH($I$5:$NI$5)=MONTH($C$2)*($I6:$NI6=D$5)))*D$4
F6:H6,D7:H9	F6	=SUM((YEAR($I$5:$NI$5)=YEAR($C$2))*(MONTH($I$5:$NI$5)=MONTH($C$2)*($I6:$NI6=F$5)))*F$4
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I4:NI4	Expression	=DIASEM(I5;2)>5	text	NO
I5:NI5	Expression	=ES.IMPAR(MES(I5))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=Dates

Attendance points.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	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT
1
2		Month	ene-23
3									ene																															feb																												mar
4				1	0,25	0,5	2	0,25	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi	sá	do	lu	ma	mi	ju	vi
5			Total	A	TE<	TE>	N	P	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
6		Employee	6	1	0,25	0,5	4	0,25		P			TE>						TE<						N							A						N
7		John Connor	4,25	2	0,25	-	2	-				N												A			A						TE<								A
8		Walter White	-	-	-	-	-	-																																		TE<																	TE>	TE>	TE>
9		Clark Kent	-	-	-	-	-	-																																					N	N	N	P	P
Hoja1

Cell Formulas
Range	Formula
I3:CT3	I3	=IF(DAY(I5)=1,TEXT(I5,"mmm"),"")
I4:CT4	I4	=TEXT(I5,"ddd")
C6:C9	C6	=SUM(D6:H6)
D6:E6	D6	=SUM((YEAR($I$5:$NI$5)=YEAR($C$2))*(MONTH($I$5:$NI$5)=MONTH($C$2)*($I6:$NI6=D$5)))*D$4
F6:H6,D7:H9	F6	=SUM((YEAR($I$5:$NI$5)=YEAR($C$2))*(MONTH($I$5:$NI$5)=MONTH($C$2)*($I6:$NI6=F$5)))*F$4
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I4:NI4	Expression	=DIASEM(I5;2)>5	text	NO
I5:NI5	Expression	=ES.IMPAR(MES(I5))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=Dates

 

[felixstraube]

Sorry something went wrong and i can not edit the previous post:

Here is the link:

Attendance points.zip