Retrieve only dates within a specific month

[qwzky]

Hello! In range S6:S22 there are some random dates of user input. I want the dates within September to be retrieved in range T6:T22 (unfilled cells should remain empty: "", without showing any error). Is there a formula to do this, without getting VBA involved?

PCO final3.xlsx
	S	T
5	General	septembrie
6	04.06.2023	04.06.2023
7
8	16.09.2023	16.09.2023
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Data

Cell Formulas
Range	Formula
T6:T22	T6	=FILTER(S6:S22,PCO!C4)
S6:S22	S6	=IF(PCO!$D10="","",PCO!$D10)
Dynamic array formulas.

 

[Bosquedeguate]

Try this ...

The month column (Column T) contains a date and formatted as "mmmm" (to display the full month name).
The formula matches the Month (numerical value) with the month of the values in Column S.

RotatingWorkDay.xlsm
	S	T
1	General	September
2	4/6/2023
3
4	9/16/2023	9/16/2023
5
6
7
8	5/1/2023
9
10
11
12	9/27/2023	9/27/2023
13
14
15
16
17
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
T2:T22	T2	=IF(S2=0,"",IF(MONTH(S2)=MONTH(T$1),S2,""))

Here is a test with the date in T1 changed to April 1, 2023 (4/1/2023)

RotatingWorkDay.xlsm
	S	T
1	General	April
2	4/6/2023	4/6/2023
3
4	9/16/2023
5
6
7
8	5/1/2023
9
10
11
12	9/27/2023
13
14
15
16
17
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
T2:T22	T2	=IF(S2=0,"",IF(MONTH(S2)=MONTH(T$1),S2,""))

 

[qwzky]

Try this ...

The month column (Column T) contains a date and formatted as "mmmm" (to display the full month name).
The formula matches the Month (numerical value) with the month of the values in Column S.

RotatingWorkDay.xlsm
	S	T
1	General	September
2	4/6/2023
3
4	9/16/2023	9/16/2023
5
6
7
8	5/1/2023
9
10
11
12	9/27/2023	9/27/2023
13
14
15
16
17
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
T2:T22	T2	=IF(S2=0,"",IF(MONTH(S2)=MONTH(T$1),S2,""))

Here is a test with the date in T1 changed to April 1, 2023 (4/1/2023)

RotatingWorkDay.xlsm
	S	T
1	General	April
2	4/6/2023	4/6/2023
3
4	9/16/2023
5
6
7
8	5/1/2023
9
10
11
12	9/27/2023
13
14
15
16
17
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
T2:T22	T2	=IF(S2=0,"",IF(MONTH(S2)=MONTH(T$1),S2,""))

It doesn't work Look here what it gives me:

PCO final3.xlsx
	S	T
5	General	septembrie
6	04.06.2023	00.01.1900
7		00.01.1900
8	16.09.2023	00.01.1900
9		00.01.1900
10		00.01.1900
11		00.01.1900
12		00.01.1900
13		00.01.1900
14		00.01.1900
15		00.01.1900
16		00.01.1900
17		00.01.1900
18		00.01.1900
19		00.01.1900
20		00.01.1900
21		00.01.1900
22		00.01.1900
Data

Cell Formulas
Range	Formula
S6:S22	S6	=IF(PCO!$D10="","",PCO!$D10)
T6:T22	T6	=IF(S6=0,"",IF(MONTH(S6)=MONTH(T$6),S6,""))

 

[FlameRetired]

Your Office Version(s) shown are 2016, 2013 yet one formula shown has a FILTER function. FILTER is not introduced until 2021. What versions does this need to work with?

 

[qwzky]

oh, i forgot to update my profile. I have 2021

 

[FlameRetired]

Editied: mistake in the formula and cell reference. Corrected here.

With 9/1/2023 in S4 this in T6 and copied down.

=IF(INDEX(FREQUENCY(S6,EOMONTH($S$4,{-1,0})),2),S6,"")

 

[FlameRetired]

oh, i forgot to update my profile. I have 2021

Thank you. Our posts crossed in the ether. Does my formula in my previous post work for you? If not do you still want a FILTER solution?

Editied: mistake in the formula and cell reference. Corrected here.

With 9/1/2023 in S4 this in T6 and copied down.

=IF(INDEX(FREQUENCY(S6,EOMONTH($S$4,{-1,0})),2),S6,"")

For a FILTER solution with 9/1/2023 in S4 this in T6

=FILTER(S6:S22,(S6:S22>EOMONTH(S4,-1))*(S6:S22<=EOMONTH(S4,0)))

it should spill all relevant dates.

 

[qwzky]

Thank you. Our posts crossed in the ether. Does my formula in my previous post work for you? If not do you still want a FILTER solution?

For a FILTER solution with 9/1/2023 in S4 this in T6

=FILTER(S6:S22,(S6:S22>EOMONTH(S4,-1))*(S6:S22<=EOMONTH(S4,0)))

it should spill all relevant dates.

This works brilliantly Love it. Thank you, thank you, thank you

 

[FlameRetired]

This works brilliantly Love it. Thank you, thank you, thank you

You are welcome. Thank you for the feedback.

 

[Bosquedeguate]

It doesn't work Look here what it gives me:

PCO final3.xlsx
	S	T
5	General	septembrie
6	04.06.2023	00.01.1900
7		00.01.1900
8	16.09.2023	00.01.1900
9		00.01.1900
10		00.01.1900
11		00.01.1900
12		00.01.1900
13		00.01.1900
14		00.01.1900
15		00.01.1900
16		00.01.1900
17		00.01.1900
18		00.01.1900
19		00.01.1900
20		00.01.1900
21		00.01.1900
22		00.01.1900
Data

Cell Formulas
Range	Formula
S6:S22	S6	=IF(PCO!$D10="","",PCO!$D10)
T6:T22	T6	=IF(S6=0,"",IF(MONTH(S6)=MONTH(T$6),S6,""))

You did not set the column header (e.g. September) to a date value. And format the column header as a custom format "mmmm".
That is why the error was generated.