Function Problems

How to Return the Monday of a Week in Excel (Formula Guide)

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs an Excel formula to automatically calculate and return the date of the Monday that begins the week for a specific date located in another cell.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Organizing timesheets, creating weekly schedules, or standardizing report data where all dates need to be aligned to the start of their respective weeks.
Observed behavior: When given a specific date (e.g., 9/20/2024), the formula correctly calculates and outputs the date of the preceding Monday (9/16/2024).

Before you start

Ensure that the cell containing your original date is properly formatted as a Date, and that the cell where you intend to input the formula is also set to the Date format to prevent the result from displaying as a serial number.

Solution 1Recommended

Use the WEEKDAY Function to Find Monday

Subtract the weekday sequence number from the original date to roll it back to Monday.

The WEEKDAY function in Excel returns a number representing the day of the week for a specific date. By adding the return_type parameter '3', the function assigns Monday as 0, Tuesday as 1, and so on up to Sunday as 6. Subtracting this value from your original date perfectly rolls the date back to Monday.

Select the destination cell

Click on an empty cell where you want the calculated Monday date to be displayed.

Input the WEEKDAY formula

Assuming your original date is located in cell A2, type the following formula into the formula bar: =A2-WEEKDAY(A2,3).

Press Enter to apply

Press the Enter key. If the formula returns a 5-digit number (like 45551), right-click the cell, select 'Format Cells', navigate to the 'Number' tab, and choose 'Date'.

Alternative return_types: If you need your week to start on Sunday instead of Monday, you can use the default return type by modifying the formula to: =A2-WEEKDAY(A2,1)+1.

Efficient Spreadsheet Software

Calculate Dates Easily with WPS Spreadsheet

WPS Spreadsheet fully supports all standard Excel date functions, including WEEKDAY, enabling you to effortlessly manage schedules, automate timesheets, and track weekly metrics.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your dates.
2. Enter the formula: Click the cell next to your date (e.g., A2) and input =A2-WEEKDAY(A2,3).
3. Drag to fill: Press Enter, then click and drag the fill handle at the bottom-right of the cell to apply the Monday calculation to the rest of your column.

100% compatibility with Microsoft Excel (.xlsx) formats and date functions.Completely free and lightweight, ensuring fast performance on any device.Familiar user interface that requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

How do I return the Monday of the current week?

You can combine the WEEKDAY function with the TODAY() function. Use the formula =TODAY()-WEEKDAY(TODAY(),3) to dynamically return the Monday of whatever the current week happens to be.

Why is my formula returning a 5-digit number instead of a date?

This happens because Excel and WPS Spreadsheet store dates as sequential serial numbers for calculation purposes. To fix the display, simply select the cell, go to the Home tab, and change the Number Format dropdown from 'General' to 'Short Date'.

How can I find the Friday of a given week?

Since Friday is exactly 4 days after Monday, you can use the Monday formula and simply add 4 at the end: =A2-WEEKDAY(A2,3)+4.

Does the WEEKDAY formula work identically in WPS Spreadsheet?

Yes. WPS Spreadsheet provides full functional compatibility with Microsoft Excel. The WEEKDAY function, its parameters, and all date arithmetic logic work exactly the same way.

Excel & Data Problems