Fiscal Year ending on the Friday closest to Nov 30th?

[mikeniedert]

I can't seem to figure out a formula that will provide the correct date. If a company's fiscal calendar ends on the Friday closest to November 30 each year. What is the formula that will provide the date of that Friday? Assume I have a list of Fiscal Years (2024, 2025, 2026, etc.) and I want to know the date which each Fiscal Year ends...and that date is always the Friday closest to November 30. Here's the list of fiscal year-ends for the next few years:

11/29/2019​

11/27/2020​

12/3/2021​

12/2/2022​

12/1/2023​

11/29/2024​

11/28/2025​

11/27/2026​

12/3/2027​

12/1/2028​

 

[Shloime]

example if dates are in column a starting cell "a1"
=IF(WEEKDAY($A1)<3,$A1-WEEKDAY($A1)-1,$A1-WEEKDAY($A1)+6)

 

[PeteWright]

Here's a plain Excel Formula that should work in all Excel Versions. No Arrays, no Lambda, no "new" functions, no VBA.
You only need one cell that contains the year, so if A1 contains the year than your formula would look like this:

=DATE(A1,11,30)-4+SEARCH(1,--(WEEKDAY(DATE(A1,11,30)-3,15)*1=1)&--(WEEKDAY(DATE(A1,11,30)-2,15)*1=1)&--(WEEKDAY(DATE(A1,11,30)-1,15)*1=1)&--(WEEKDAY(DATE(A1,11,30),15)*1=1)&--(WEEKDAY(DATE(A1,11,30)+1,15)=1)&--(WEEKDAY(DATE(A1,11,30)+2,15)=1)&--(WEEKDAY(DATE(A1,11,30)+3,15)=1))

Book1.xlsm
	A	B
1	2019	11/29/2019
2	2020	11/27/2020
3	2021	12/3/2021
4	2022	12/2/2022
5	2023	12/1/2023
6	2024	11/29/2024
7	2025	11/28/2025
8	2026	11/27/2026
9	2027	12/3/2027
10	2028	12/1/2028
Master

Cell Formulas
Range	Formula
B1:B10	B1	=DATE(A1,11,30)-4+SEARCH(1,--(WEEKDAY(DATE(A1,11,30)-3,15)*1=1)&--(WEEKDAY(DATE(A1,11,30)-2,15)*1=1)&--(WEEKDAY(DATE(A1,11,30)-1,15)*1=1)&--(WEEKDAY(DATE(A1,11,30),15)*1=1)&--(WEEKDAY(DATE(A1,11,30)+1,15)=1)&--(WEEKDAY(DATE(A1,11,30)+2,15)=1)&--(WEEKDAY(DATE(A1,11,30)+3,15)=1))

If you don't want any "helper cell", for a plain Excel Formula you should replace "A1" with the year.

 

[Dave Patton]

Another solution and I included the previous suggestions.
It is a good idea to include the Excel version information in your profile.

Dates andTime 2024.xlsm
	A	B	C	D	E	F
1	30-11-19	Nov 29, 2019	Nov 29, 2019	2019	Nov 29, 2019	Nov 29, 2019
2	30-11-20	Nov 27, 2020	Nov 27, 2020	2020	Nov 27, 2020	Nov 27, 2020
3	30-11-21	Dec 03, 2021	Dec 03, 2021	2021	Dec 03, 2021	Dec 03, 2021
4	30-11-22	Dec 02, 2022	Dec 02, 2022	2022	Dec 02, 2022	Dec 02, 2022
5	30-11-23	Dec 01, 2023	Dec 01, 2023	2023	Dec 01, 2023	Dec 01, 2023
6	30-11-24	Nov 29, 2024	Nov 29, 2024	2024	Nov 29, 2024	Nov 29, 2024
7	30-11-25	Nov 28, 2025	Nov 28, 2025	2025	Nov 28, 2025	Nov 28, 2025
8	30-11-26	Nov 27, 2026	Nov 27, 2026	2026	Nov 27, 2026	Nov 27, 2026
9	30-11-27	Dec 03, 2027	Dec 03, 2027	2027	Dec 03, 2027	Dec 03, 2027
10	30-11-28	Dec 01, 2028	Dec 01, 2028	2028	Dec 01, 2028	Dec 01, 2028
11	30-11-29	Nov 30, 2029	Nov 30, 2029	2029	Nov 30, 2029	Nov 30, 2029
12	30-11-30	Nov 29, 2030	Nov 29, 2030	2030	Nov 29, 2030	Nov 29, 2030
13	30-11-31	Nov 28, 2031	Nov 28, 2031	2031	Nov 28, 2031	Nov 28, 2031
14	30-11-32	Dec 03, 2032	Dec 03, 2032	2032	Dec 03, 2032	Dec 03, 2032
15	30-11-33	Dec 02, 2033	Dec 02, 2033	2033	Dec 02, 2033	Dec 02, 2033
16	30-11-34	Dec 01, 2034	Dec 01, 2034	2034	Dec 01, 2034	Dec 01, 2034
17	30-11-35	Nov 30, 2035	Nov 30, 2035	2035	Nov 30, 2035	Nov 30, 2035
18	30-11-36	Nov 28, 2036	Nov 28, 2036	2036	Nov 28, 2036	Nov 28, 2036
19	30-11-37	Nov 27, 2037	Nov 27, 2037	2037	Nov 27, 2037	Nov 27, 2037
20	30-11-38	Dec 03, 2038	Dec 03, 2038	2038	Dec 03, 2038	Dec 03, 2038
21	30-11-39	Dec 02, 2039	Dec 02, 2039	2039	Dec 02, 2039	Dec 02, 2039
22	30-11-40	Nov 30, 2040	Nov 30, 2040	2040	Nov 30, 2040	Nov 30, 2040
23	30-11-41	Nov 29, 2041	Nov 29, 2041	2041	Nov 29, 2041	Nov 29, 2041
24	30-11-42	Nov 28, 2042	Nov 28, 2042	2042	Nov 28, 2042	Nov 28, 2042
25	30-11-43	Nov 27, 2043	Nov 27, 2043	2043	Nov 27, 2043	Nov 27, 2043
26	30-11-44	Dec 02, 2044	Dec 02, 2044	2044	Dec 02, 2044	Dec 02, 2044
27	30-11-45	Dec 01, 2045	Dec 01, 2045	2045	Dec 01, 2045	Dec 01, 2045
28	30-11-46	Nov 30, 2046	Nov 30, 2046	2046	Nov 30, 2046	Nov 30, 2046
29	30-11-47	Nov 29, 2047	Nov 29, 2047	2047	Nov 29, 2047	Nov 29, 2047
30	30-11-48	Nov 27, 2048	Nov 27, 2048	2048	Nov 27, 2048	Nov 27, 2048
1f

Cell Formulas
Range	Formula
B1:B30	B1	=IF(WEEKDAY($A1)<3,$A1-WEEKDAY($A1)-1,$A1-WEEKDAY($A1)+6)
C1:C30	C1	=LET(y,WORKDAY.INTL(A1+1,-1,"1111011"),IF((30-DAY(y))<=3,y,y+7))
E1:E30	E1	=DATE(D1,11,30)-4+SEARCH(1,--(WEEKDAY(DATE(D1,11,30)-3,15)*1=1)&--(WEEKDAY(DATE(D1,11,30)-2,15)*1=1)&--(WEEKDAY(DATE(D1,11,30)-1,15)*1=1)&--(WEEKDAY(DATE(D1,11,30),15)*1=1)&--(WEEKDAY(DATE(D1,11,30)+1,15)=1)&--(WEEKDAY(DATE(D1,11,30)+2,15)=1)&--(WEEKDAY(DATE(D1,11,30)+3,15)=1))
F1:F30	F1	=LET(y,WORKDAY.INTL(DATE(D1,11,30)+1,-1,"1111011"),IF((30-DAY(y))<=3,y,y+7))