'%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 Calculate Monday and Wednesday Deadlines Before a Sale in Excel
Question details
The user needs to calculate the Monday and Wednesday before a given sale date, as well as the first business day after the sale, while optionally excluding public holidays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up automated deadlines for pre-sale and post-sale events in a spreadsheet without scheduling tasks on weekends or holidays.
- Observed behavior
- Requires a specialized formula combination that targets specific days of the week prior to a reference date and automatically shifts to the nearest business day if the calculated day falls on a holiday.
Prepare a list of your local public holiday dates in an empty column and define it as a named range called 'Holidays' to ensure accurate business day calculations.
Use WORKDAY and WEEKDAY Functions to Calculate Deadlines
Combine the WORKDAY and WEEKDAY functions to offset days backward to a specific weekday (Monday or Wednesday) and dynamically skip non-working days or holidays.
The WORKDAY function calculates business days, automatically excluding weekends and optional holidays. By nesting the WEEKDAY function inside it with specific return types (like 12 for Monday and 14 for Wednesday offsets), you can force Excel to step back to the exact previous weekday you need.
Type your public holiday dates in a column. Select the cells containing the dates, click into the Name Box (to the left of the formula bar), type 'Holidays', and press Enter.
Assuming your sale date is located in cell A2, select the cell where you want the Monday deadline to appear and enter the formula: =WORKDAY(A2-WEEKDAY(A2,12)+1,-1,Holidays). Press Enter.
Select the cell for the Wednesday deadline and enter the formula: =WORKDAY(A2-WEEKDAY(A2,14)+1,-1,Holidays). Press Enter.
To find the first available business day immediately following the sale date, select a cell and enter the formula: =WORKDAY(A2,1,Holidays).
Calculate Dynamic Deadlines Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions, allowing you to calculate complex pre-sale deadlines, offset specific weekdays, and manage schedules seamlessly.
- 1. Input Your Data: Launch WPS Spreadsheet, enter your sale dates in column A, and list your holidays in an empty column.
- 2. Define the Holiday Range: Highlight your holiday dates, click the Name Box, type 'Holidays', and hit Enter.
- 3. Apply the Formula: Use =WORKDAY(A2-WEEKDAY(A2,12)+1,-1,Holidays) in your target cell to instantly generate your dynamic Monday deadline.
Frequently Asked Questions
What do the return types '12' and '14' in the WEEKDAY function mean?
The second argument in the WEEKDAY function specifies how the days of the week are numbered. Using '12' sets Tuesday as day 1 (making Monday the 7th day), and '14' sets Thursday as day 1 (making Wednesday the 7th day). This helps the formula accurately calculate the offset needed to find the preceding Monday and Wednesday.
What happens to the deadline if the calculated Monday is a public holiday?
Because the WORKDAY function is set to step back by one workday (-1) from the offset date and references your 'Holidays' list, it will skip the holiday and return the immediately preceding business day. For a Monday holiday, this is usually the preceding Friday.
Why does my deadline formula return a strange 5-digit number?
Spreadsheet programs store dates as sequential serial numbers. If you see a 5-digit number (like 45450), the calculation is correct, but the cell formatting is wrong. Right-click the cell, select 'Format Cells', choose 'Date', and pick your preferred date format.
