Return date of next instance

[IluvPivots]

Excel 2016. What would the formula be in the End date column? I want it to return the date of the next instance of "ENDDATE". The end date for week 10/5/2024 is 10/26/2024. See example column.

Date	Label	END DATE		Example of result
10/5/2024​				10/26/2024​
10/12/2024​				10/26/2024​
10/19/2024​				10/26/2024​
10/26/2024​	ENDDATE			10/26/2024​
11/2/2024​				11/30/2024​
11/9/2024​				11/30/2024​
11/16/2024​				11/30/2024​
11/23/2024​				11/30/2024​
11/30/2024​	ENDDATE			11/30/2024​
12/7/2024​				12/28/2024​
12/14/2024​				12/28/2024​
12/21/2024​				12/28/2024​
12/28/2024​	ENDDATE			12/28/2024​
1/4/2025​				2/1/2025​
1/11/2025​				2/1/2025​
1/18/2025​				2/1/2025​
1/25/2025​				2/1/2025​
2/1/2025​	ENDDATE			2/1/2025​

 

[hagia_sofia]

Hello, one option could be:

=INDEX(A2:A$19,MATCH("ENDDATE",B2:B$19,0))

 

[IluvPivots]

Hello, one option could be:

=INDEX(A2:A$19,MATCH("ENDDATE",B2:B$19,0))

This works! Thank you. I added an IF statement that if it is an ENDDATE row it should return that value.

 

[HighAndWilder]

Excel 2016. What would the formula be in the End date column? I want it to return the date of the next instance of "ENDDATE". The end date for week 10/5/2024 is 10/26/2024. See example column.

Date	Label	END DATE		Example of result
10/5/2024​				10/26/2024​
10/12/2024​				10/26/2024​
10/19/2024​				10/26/2024​
10/26/2024​	ENDDATE			10/26/2024​
11/2/2024​				11/30/2024​
11/9/2024​				11/30/2024​
11/16/2024​				11/30/2024​
11/23/2024​				11/30/2024​
11/30/2024​	ENDDATE			11/30/2024​
12/7/2024​				12/28/2024​
12/14/2024​				12/28/2024​
12/21/2024​				12/28/2024​
12/28/2024​	ENDDATE			12/28/2024​
1/4/2025​				2/1/2025​
1/11/2025​				2/1/2025​
1/18/2025​				2/1/2025​
1/25/2025​				2/1/2025​
2/1/2025​	ENDDATE			2/1/2025​

Will the end date always be the last Saturday in the month? If it is then the Label column won't be needed.

=SUM(IF(WEEKDAY(EOMONTH($A2,0)-{0,1,2,3,4,5,6})=7,EOMONTH($A2,0)-{0,1,2,3,4,5,6}),0)

 

[IluvPivots]

Will the end date always be the last Saturday in the month? If it is then the Label column won't be needed.

=SUM(IF(WEEKDAY(EOMONTH($A2,0)-{0,1,2,3,4,5,6})=7,EOMONTH($A2,0)-{0,1,2,3,4,5,6}),0)

This is awesome! However, the business rule states end of month is the last saturday UNLESS the first day of next month is on a saturday.

 

[HighAndWilder]

This is awesome! However, the business rule states end of month is the last saturday UNLESS the first day of next month is on a saturday.

=IF(WEEKDAY((EOMONTH($A2,0)+1))=7,EOMONTH($A2,0)+1,SUM(IF(WEEKDAY(EOMONTH($A2,0)-{0,1,2,3,4,5,6})=7,EOMONTH($A2,0)-{0,1,2,3,4,5,6}),0))

For testing purposes, the following dates are the first day of the month where it is a Saturday. UK date formatting.

01/02/2025

01/03/2025

01/11/2025

01/08/2026

01/05/2027

01/01/2028

01/04/2028

01/07/2028

01/09/2029

01/12/2029

01/06/2030

 

[IluvPivots]

=IF(WEEKDAY((EOMONTH($A2,0)+1))=7,EOMONTH($A2,0)+1,SUM(IF(WEEKDAY(EOMONTH($A2,0)-{0,1,2,3,4,5,6})=7,EOMONTH($A2,0)-{0,1,2,3,4,5,6}),0))

For testing purposes, the following dates are the first day of the month where it is a Saturday. UK date formatting.

01/02/2025

01/03/2025

01/11/2025

01/08/2026

01/05/2027

01/01/2028

01/04/2028

01/07/2028

01/09/2029

01/12/2029

01/06/2030

Wow!! Where has this formula been all my time in this job! Spectacular! Many thanks.

 

[Joe4]

IluvePivots,
In the future, when marking a post as the solution, please mark the original post containing the solution (and not your own post acknowledging that some other post is the solution).
I have updated this thread for you.

 

[Phuoc]

. /

 

[IluvPivots]

Another option:

Book1
	A	B	C	D	E
1	Date	Label	END DATE		Example of result
2	10/5/2024		10/26/2024		10/26/2024
3	10/12/2024		10/26/2024		10/26/2024
4	10/19/2024		10/26/2024		10/26/2024
5	10/26/2024	ENDDATE	10/26/2024		10/26/2024
6	11/2/2024		11/30/2024		11/30/2024
7	11/9/2024		11/30/2024		11/30/2024
8	11/16/2024		11/30/2024		11/30/2024
9	11/23/2024		11/30/2024		11/30/2024
10	11/30/2024	ENDDATE	11/30/2024		11/30/2024
11	12/7/2024		12/28/2024		12/28/2024
12	12/14/2024		12/28/2024		12/28/2024
13	12/21/2024		12/28/2024		12/28/2024
14	12/28/2024	ENDDATE	12/28/2024		12/28/2024
15	1/4/2025		2/1/2025		2/1/2025
16	1/11/2025		2/1/2025		2/1/2025
17	1/18/2025		2/1/2025		2/1/2025
18	1/25/2025		2/1/2025		2/1/2025
19	2/1/2025	ENDDATE	2/1/2025		2/1/2025
Sheet1

Cell Formulas
Range	Formula
C2:C19	C2	=INDEX(A2:$A$19,MATCH("ENDDATE",B2:$B$19,0))

thank you