Pick the last date of the month

[tanvirabid3]

Hi everyone,
I have the daily dates for all the months of 2021. The catch is that for some months the last date is the EMONTH date like Jan 31. But for some months the last date is March 27, April 28, June 26 and so on. I want to pick the last available date for every month from Jan to Dec. Is there a way that this can be done. Thanks

 

[earthworm]

Hi everyone,
I have the daily dates for all the months of 2021. The catch is that for some months the last date is the EMONTH date like Jan 31. But for some months the last date is March 27, April 28, June 26 and so on. I want to pick the last available date for every month from Jan to Dec. Is there a way that this can be done. Thanks

Below function

Book1
	A	B
1	05-01-21	31-Jan-21
Sheet1

Cell Formulas
Range	Formula
B1	B1	=EOMONTH(A1,0)

 

[tanvirabid3]

Below function

Book1
	A	B
1	05-01-21	31-Jan-21
Sheet1

Cell Formulas
Range	Formula
B1	B1	=EOMONTH(A1,0)

Hi there, this is the just the general formula. Let me clarify please. I want to pick the latest date in a month from the given dates. Like in Feb, the latest date is Feb 25. In March it is March 26. I want to find the last dates for every month that are in the list. Please assist.

 

[hayda]

e.g. 11.02.2021

=EOMONTH(A1;0) >>>> 28.02.2021
=EOMONTH(A1;1) >>>> 31.03.2021
=EOMONTH(A1;2) >>>> 30.04.2021

 

[earthworm]

Another approach

Book1
	A	B	C	D	E	F
1	01-01-21	1-2021		1-2021	Jan-2021	9-Jan-21
2	06-01-21	1-2021		2-2021	Feb-2021	8-Feb-21
3	07-01-21	1-2021		3-2021	Mar-2021	3-Mar-21
4	08-01-21	1-2021		4-2021	Apr-2021
5	09-01-21	1-2021		5-2021	May-2021
6	01-02-21	2-2021		6-2021	Jun-2021
7	08-02-21	2-2021		7-2021	Jul-2021
8	03-03-21	3-2021		8-2021	Aug-2021
Sheet1

Cell Formulas
Range	Formula
E1:E8	E1	=TEXT(D1,"MMM-YYY")
F1:F8	F1	=IFERROR(INDEX($A$1:$A$8,AGGREGATE(14,6,ROW($B$1:$B$8)-ROW($B$1)+1/($B$1:$B$8=D1),1),1),"")
B1:B8	B1	=MONTH(A1)&"-"&YEAR(A1)&""

 

[Dave Patton]

try Maxifs

Maxif.xlsm
	A	B	C
1	1-Jan-21	Jan-21	9-Jan-21
2	6-Jan-21	Feb-21	8-Feb-21
3	7-Jan-21	Mar-21	3-Mar-21
4	8-Jan-21	Apr-21	28-Apr-21
5	9-Jan-21
6	1-Feb-21
7	8-Feb-21
8	3-Mar-21
9	4-Apr-21
10	22-Apr-21
11	28-Apr-21
1a

Cell Formulas
Range	Formula
C1:C4	C1	=MAXIFS($A$1:$A$11,$A$1:$A$11,">="&B1,$A$1:$A$11,"<="&EOMONTH(B1,0))

 

[tanvirabid3]

try Maxifs

Maxif.xlsm
	A	B	C
1	1-Jan-21	Jan-21	9-Jan-21
2	6-Jan-21	Feb-21	8-Feb-21
3	7-Jan-21	Mar-21	3-Mar-21
4	8-Jan-21	Apr-21	28-Apr-21
5	9-Jan-21
6	1-Feb-21
7	8-Feb-21
8	3-Mar-21
9	4-Apr-21
10	22-Apr-21
11	28-Apr-21
1a

Cell Formulas
Range	Formula
C1:C4	C1	=MAXIFS($A$1:$A$11,$A$1:$A$11,">="&B1,$A$1:$A$11,"<="&EOMONTH(B1,0))

Hi Dave, thanks. What are the formulas for column B. I mean B1, B2, B3 and B4.

 

[Peter_SSs]

Could this be what you are after?

22 01 13.xlsm
	A	B	C
1	01-Jan-21		09-Jan-21
2	06-Jan-21		08-Feb-21
3	07-Jan-21		31-Mar-21
4	08-Jan-21		28-Apr-21
5	09-Jan-21
6	01-Feb-21		06-Jun-21
7	08-Feb-21
8	04-Apr-21		14-Aug-21
9	22-Apr-21
10	28-Apr-21
11	06-Jun-21
12	02-Jun-21		15-Dec-21
13	15-Dec-21
14	12-Dec-21
15	14-Aug-21
16	31-Mar-21
17	01-Mar-21
18
Last in month

Cell Formulas
Range	Formula
C1:C12	C1	=IFERROR(MAX(FILTER(A1:A100,MONTH(A1:A100)=ROWS(C$1:C1))),"")

 

[tanvirabid3]

Hi everyone, I think I haven't been able to explain my issue clearly. The following is the image:

Column A
For Dec 2017, the last day is 12/31/2017
For Jan 2018, the last day is 1/31/2018
For Feb 2018, the last day is 2/25/2018
For Mar 2018, the last day is 3/26/2018
For April 2018, the last day is 4/29/2018

The desire is that in Column B
For Dec 2017 all dates, the last day i.e. 12/31/2017 should show.
For Jan 2018 all dates, the last day i.e. 1/31/2018 should show.
For Feb 2018 all dates, the last day i.e. 2/25/2018 should show.
For Mar 2018 all dates, the last day i.e. 3/26/2018 should show.
For April 2018 all dates, the last day i.e. 4/29/2018 should show.

Thanks everyone for your help so far.

 

[Peter_SSs]

Like this then?

22 01 13.xlsm
	A	B
1	01-Jan-21	09-Jan-21
2	06-Jan-21	09-Jan-21
3	07-Jan-21	09-Jan-21
4	08-Jan-21	09-Jan-21
5	09-Jan-21	09-Jan-21
6	01-Feb-21	08-Feb-21
7	08-Feb-21	08-Feb-21
8	01-Mar-21	31-Mar-21
9	31-Mar-21	31-Mar-21
10	04-Apr-21	28-Apr-21
11	22-Apr-21	28-Apr-21
12	28-Apr-21	28-Apr-21
13	02-Jun-21	06-Jun-21
14	06-Jun-21	06-Jun-21
15	14-Aug-21	14-Aug-21
16	12-Dec-21	15-Dec-21
17	15-Dec-21	15-Dec-21
Last in month

Cell Formulas
Range	Formula
B1:B17	B1	=MAX(FILTER(A1:A17,TEXT(A1:A17,"mmyy")=TEXT(A1,"mmyy")))