filter by begaining of each first month till end

[excelNewbie22]

hi!

=SORT(FILTER(M2:M60,(NOT(ISNUMBER(XMATCH(M2:M60,C3:C22)))*(M2:M60<>""))))

i have this list of names in column M,
and a shorter list of names in column c,
and i subtract the lists,
the thing is the list in column c is part of a table goes by date (in col A)
i want to add to the filter, also by date,
but a dynamic one,
from the first of each month to it's end, without specific beginning or end (like 1/9/24 and 30/9/24),
is it possible?

p.s maybe add an option to edit the title? i misspell it and can't fix it

 

[hagia_sofia]

Could you please post a sample of how your data is structured and how should the desired output look like?

 

[excelNewbie22]

ok, changed the cloumns,
i want to modify the formula in f2 for filtering also, by current month,
so, if we are in september, i want all those who didn't paid yet from 1/9/2024 to 30/9/2024, without telling the formula specific date (like 1/9/24 and etc)

add one column (g) (not a must for me but optionally): another filter formula which can filter by specific month only?

sheet1
	A	B	C	D	E	F	G
1	NAMES	date		paid		didn't paid for current month	paid in augost
2	joe	28/08/2024		joe		abe
3	jack	01/09/2024		roy		charlie
4	abe	09/09/2024		james		jack
5	charlie	10/09/2024		betti		rosa
6	betti	15/09/2024
7	roy	22/09/2024
8	rosa	01/10/2024
9	james	10/10/2024
10
test

Cell Formulas
Range	Formula
F2:F5	F2	=SORT(FILTER(A2:A60,(NOT(ISNUMBER(XMATCH(A2:A60,D2:D21)))*(A2:A60<>""))))
Dynamic array formulas.

 

[hagia_sofia]

As for the former question please test the following if it serves the purpose:

=SORT(FILTER(A2:A60,NOT(ISNUMBER(XMATCH(A2:A60,D2:D21)))*(A2:A60<>"")*(B2:B60>=EOMONTH(TODAY(),-1)+1)*(B2:B60<=EOMONTH(TODAY(),0)))))

As far as the latter is concerned it is doable but a) how is the formula for those who paid constructed, i.e. how to identify them and b) do would you prefer to select a month - e.g. within the formula or within some cells?

 

[excelNewbie22]

thanks sofia, but this isn't it:
i updated the table for more clarity (or for fixing my explaining last time)

test
	A	B	C	D	E	F	G	H
1	full list of names		names who paid -> by date	date paid		didn't paid this month	should be	because
2	joe		jack	28/08/2024		abe	joe	still didn't pay this month = septemberr
3	jack		charlie	01/09/2024			jack	paid only in august
4	abe		roy	22/09/2024			abe	still didn't pay this month = septemberr
5	charlie		rosa	29/08/2024			betti	still didn't pay this month = septemberr
6	betti		james	30/08/2024			rosa	paid only in august
7	roy						james	paid only in august
8	rosa
9	james
test

Cell Formulas
Range	Formula
F2	F2	=SORT(FILTER(A2:A60,NOT(ISNUMBER(XMATCH(A2:A60,C2:C21)))*(A2:A60<>"")*(D2:D60>=EOMONTH(TODAY(),-1)+1)*(D2:D60<=EOMONTH(TODAY(),0))))

for the second problem:
identify them by the date column,
selecting a month - preferble by cell like i2 or i3 will be presenting the month

test
	I
2	aug-24
3	8
test

 

[hagia_sofia]

Hello again, thanks for the clarification. Hopefully this will work:

1)

=LET(
list,A2:A9,
names,C2:C60,
dates,D2:D60,
a,FILTER(names,(dates>=EOMONTH(TODAY(),-1)+1)*(dates<=EOMONTH(TODAY(),0))),
b,XLOOKUP(list,a,a),
FILTER(list,ISNA(b)))

2)

=FILTER(C2:C6,(D2:D6>=EOMONTH(I1,-1)+1)*(D2:D6<=EOMONTH(I1,0)))

 

[excelNewbie22]

excellent !
thank you sofia!

 

[hagia_sofia]

Glad to help and apologies it took so much time...