'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find the Fourth Friday of a Month in Excel
Question details
The user needs an Excel formula to calculate the date of the fourth Friday of a specific month based on a reference date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating monthly scheduling or tracking by dynamically calculating specific weekdays in a given month.
- Observed behavior
- The user wants a dynamic formula that outputs the fourth Friday of the month corresponding to a date located in cell A1.
Ensure that your reference cell (A1) contains a valid, recognizable date format so the formula can correctly extract the month and year.
Use the Standard DAY and WEEKDAY Formula
This solution uses a combination of basic date functions to subtract the current days, calculate the start of the month, and accurately offset to the fourth Friday.
Spreadsheets handle dates as serial numbers. By taking your given date, subtracting its current day value, and then evaluating the weekday of the month's start, you can accurately pinpoint the exact date of the fourth Friday.
Click on cell A1 and enter a valid date that falls within the month you want to calculate (for example, '10/12/2023').
Select the empty cell where you want the fourth Friday to appear. Type the following formula: =A1-DAY(A1)+29-WEEKDAY(A1-DAY(A1)+2)
Press Enter. The cell will now display the exact date of the fourth Friday of that specific month.
Calculate Complex Dates Quickly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions natively. You can seamlessly use the exact same DAY and WEEKDAY formulas to calculate specific dates without worrying about compatibility issues.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your current workbook or create a new blank spreadsheet.
- 2. Input your reference date: Click cell A1 and type any date in the target month.
- 3. Apply the weekday formula: In your desired output cell, enter =A1-DAY(A1)+29-WEEKDAY(A1-DAY(A1)+2) and press Enter.
- 4. Format as Date: If the result shows as a plain number, right-click the cell, choose 'Format Cells', and select 'Date' to view it correctly.
Frequently Asked Questions
Why does my formula return a 5-digit number instead of a date?
Spreadsheets store dates as sequential serial numbers (e.g., 45220). To display this as a readable date, right-click the cell containing your formula, click 'Format Cells', go to the 'Number' tab, and select 'Date'. Choose your preferred format and click OK.
How can I find the first Friday of the month instead?
You can adjust the offset days in the formula. To find the first Friday, subtract three weeks (21 days) from the 29-day offset used for the fourth Friday. Change the formula to: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2).
Will this formula automatically update if I change the date in cell A1?
Yes, because the formula uses a dynamic cell reference (A1), any new date entered into cell A1 will immediately recalculate the formula and output the fourth Friday of that new month.
Does this formula work the same way in Google Sheets and WPS Office?
Yes, the DAY and WEEKDAY functions are standardized across all major spreadsheet applications. The exact formula will work perfectly in Microsoft Excel, Google Sheets, and WPS Spreadsheet.
