Function Problems

How to Calculate the Wednesday Two Weeks Before a Due Date in Excel

Huma Ashraf ChHuma Ashraf Ch Nov 10, 2026 869 views

Question details

The user needs to formulate a way to calculate the exact date of a Wednesday that occurs two weeks prior to a given due date.

How to Calculate the Wednesday Two Weeks Before a Due Date in Excel
Product
Excel
Device & OS
not provided
Scenario
Scheduling tasks or creating automated timelines based on project deadlines or due dates, where a milestone always falls on a Wednesday.
Observed behavior
By inputting a due date into a cell, the user requires a formula that outputs the correct Wednesday two weeks prior, regardless of which day of the week the initial due date falls on.
Before you start

Ensure that your reference due dates are formatted as actual Date values rather than text strings, otherwise the formula calculation will result in an error.

Solution 1Recommended

Use the WEEKDAY Function

Using the WEEKDAY function is the standard and most reliable method to identify reference days and subtract specific date intervals.

The WEEKDAY function calculates the day of the week corresponding to a date. By using a specific return-type argument (14), we can set Wednesday as the 7th day of our calculation week, allowing the formula to find the most recent Wednesday and subtract an additional 7 days.

1
Select the target cell

Click on the cell where you want the calculated Wednesday date to appear.

2
Enter the WEEKDAY formula

Assuming your target due date is located in cell A2, type the formula =A2-WEEKDAY(A2,14)-7 into the formula bar.

3
Apply the formula

Press Enter to execute the formula. You will now see a numeric value or a date in the cell.

4
Format as a Date

Right-click the cell, select 'Format Cells', navigate to the 'Number' tab, and choose 'Date' to ensure the result is displayed as a proper calendar date.

Use the WEEKDAY Function
Understanding the Return Type: The argument '14' in the WEEKDAY formula tells Excel to count Thursday as 1 and Wednesday as 7. This is the key to aligning the math perfectly to land on a Wednesday.
Easily Calculate Dates in WPS

Manage Complex Date Formulas Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful, user-friendly tools to handle complex date and time calculations, providing seamless performance for project management timelines.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your project scheduling spreadsheet.
  2. 2. Input your Due Date: Type your deadline into cell A2 and ensure it is formatted as a Date.
  3. 3. Apply the calculation: In cell B2, input the formula =A2-WEEKDAY(A2,14)-7 and hit Enter.
  4. 4. Verify your dates: Drag the fill handle down to apply the Wednesday formula to any other due dates in your column.
100% compatible with Microsoft Excel formulas, including WEEKDAY, MOD, and nested date functions.Intuitive Function Wizard to help build complex formulas without memorizing syntax.Free to use, lightweight, and loads spreadsheets rapidly across all devices.
microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate a different day of the week, like Monday?

You can achieve this by adjusting the return-type argument in the WEEKDAY function. To use Monday as the target day, change the return-type to 12 (where Tuesday is 1 and Monday is 7). The formula becomes =A2-WEEKDAY(A2,12)-7.

Why does my formula return a 5-digit number instead of a date?

Spreadsheet software stores dates as sequential serial numbers to allow for mathematical calculations. If you see a number like 44500, select the cell, go to the Home tab, click the Number format dropdown, and change it to 'Short Date'.

What happens to the formula if the due date is already a Wednesday?

If the original due date is a Wednesday, the formula =A2-WEEKDAY(A2,14)-7 will treat that Wednesday as the 7th day and subtract a total of 14 days, landing exactly two weeks prior.

Do these formulas work the same way in WPS Office?

Yes, WPS Spreadsheet supports both the WEEKDAY and MOD functions identically to Microsoft Excel. You can copy and paste the exact formulas without any modification.