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

 

[Peter_SSs]

.. or another possibility

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

Cell Formulas
Range	Formula
B2:B18	B2	=IF(TEXT(A2,"mmyy")=TEXT(A3,"mmyy"),B3,A2)

 

[tanvirabid3]

.. or another possibility

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

Cell Formulas
Range	Formula
B2:B18	B2	=IF(TEXT(A2,"mmyy")=TEXT(A3,"mmyy"),B3,A2)

Thank you so much. Very appreciated.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.