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

M

mikeniedert

New Member
Joined
Aug 4, 2017
Messages
6
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​
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
example if dates are in column a starting cell "a1"
=IF(WEEKDAY($A1)<3,$A1-WEEKDAY($A1)-1,$A1-WEEKDAY($A1)+6)
 
Upvote 0
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:
Excel Formula: 
=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
AB
1201911/29/2019
2202011/27/2020
3202112/3/2021
4202212/2/2022
5202312/1/2023
6202411/29/2024
7202511/28/2025
8202611/27/2026
9202712/3/2027
10202812/1/2028
Master
Cell Formulas
RangeFormula
B1:B10B1=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.
 
Upvote 0
Solution
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
ABCDEF
130-11-19Nov 29, 2019Nov 29, 20192019Nov 29, 2019Nov 29, 2019
230-11-20Nov 27, 2020Nov 27, 20202020Nov 27, 2020Nov 27, 2020
330-11-21Dec 03, 2021Dec 03, 20212021Dec 03, 2021Dec 03, 2021
430-11-22Dec 02, 2022Dec 02, 20222022Dec 02, 2022Dec 02, 2022
530-11-23Dec 01, 2023Dec 01, 20232023Dec 01, 2023Dec 01, 2023
630-11-24Nov 29, 2024Nov 29, 20242024Nov 29, 2024Nov 29, 2024
730-11-25Nov 28, 2025Nov 28, 20252025Nov 28, 2025Nov 28, 2025
830-11-26Nov 27, 2026Nov 27, 20262026Nov 27, 2026Nov 27, 2026
930-11-27Dec 03, 2027Dec 03, 20272027Dec 03, 2027Dec 03, 2027
1030-11-28Dec 01, 2028Dec 01, 20282028Dec 01, 2028Dec 01, 2028
1130-11-29Nov 30, 2029Nov 30, 20292029Nov 30, 2029Nov 30, 2029
1230-11-30Nov 29, 2030Nov 29, 20302030Nov 29, 2030Nov 29, 2030
1330-11-31Nov 28, 2031Nov 28, 20312031Nov 28, 2031Nov 28, 2031
1430-11-32Dec 03, 2032Dec 03, 20322032Dec 03, 2032Dec 03, 2032
1530-11-33Dec 02, 2033Dec 02, 20332033Dec 02, 2033Dec 02, 2033
1630-11-34Dec 01, 2034Dec 01, 20342034Dec 01, 2034Dec 01, 2034
1730-11-35Nov 30, 2035Nov 30, 20352035Nov 30, 2035Nov 30, 2035
1830-11-36Nov 28, 2036Nov 28, 20362036Nov 28, 2036Nov 28, 2036
1930-11-37Nov 27, 2037Nov 27, 20372037Nov 27, 2037Nov 27, 2037
2030-11-38Dec 03, 2038Dec 03, 20382038Dec 03, 2038Dec 03, 2038
2130-11-39Dec 02, 2039Dec 02, 20392039Dec 02, 2039Dec 02, 2039
2230-11-40Nov 30, 2040Nov 30, 20402040Nov 30, 2040Nov 30, 2040
2330-11-41Nov 29, 2041Nov 29, 20412041Nov 29, 2041Nov 29, 2041
2430-11-42Nov 28, 2042Nov 28, 20422042Nov 28, 2042Nov 28, 2042
2530-11-43Nov 27, 2043Nov 27, 20432043Nov 27, 2043Nov 27, 2043
2630-11-44Dec 02, 2044Dec 02, 20442044Dec 02, 2044Dec 02, 2044
2730-11-45Dec 01, 2045Dec 01, 20452045Dec 01, 2045Dec 01, 2045
2830-11-46Nov 30, 2046Nov 30, 20462046Nov 30, 2046Nov 30, 2046
2930-11-47Nov 29, 2047Nov 29, 20472047Nov 29, 2047Nov 29, 2047
3030-11-48Nov 27, 2048Nov 27, 20482048Nov 27, 2048Nov 27, 2048
1f
Cell Formulas
RangeFormula
B1:B30B1=IF(WEEKDAY($A1)<3,$A1-WEEKDAY($A1)-1,$A1-WEEKDAY($A1)+6)
C1:C30C1=LET(y,WORKDAY.INTL(A1+1,-1,"1111011"),IF((30-DAY(y))<=3,y,y+7))
E1:E30E1=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:F30F1=LET(y,WORKDAY.INTL(DATE(D1,11,30)+1,-1,"1111011"),IF((30-DAY(y))<=3,y,y+7))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Vesting Schedule - Formula/Formatting
Replies
1
Views
102
StephenCrump
S
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
77
Ali M
A
F
  • Question Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
188
Peter_SSs
Peter_SSs
M
  • Solved
Formula to calculate length of the contract using Begin date and end date
Replies
5
Views
463
Micron
M
I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
30
Views
401
ItalianPlatinum
I

Forum statistics

Threads
1,223,886
Messages
6,175,189
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top