To return the Monday of the week containing a date in A2, use =A2-WEEKDAY(A2,2)+1. To return Sunday instead, use =A2-WEEKDAY(A2,1)+1; both formulas assume A2 contains a valid Excel date and return a date you can format for display.
The basic formula and what it returns
Suppose A2 contains January 15, 2026. That date is a Thursday, so the Monday-start formula returns January 12, 2026. The Sunday-start formula returns January 11, 2026. The result is the first day of the same calendar week as the original date, not the nearest Monday or Sunday in either direction.
In Excel for Microsoft 365 on Windows, enter the formula in another cell, such as B2, and press Enter. If A2 is a real date value, the result will also be a date value. If the result appears as a number, Excel is showing the date’s serial number rather than a human-readable date; apply a date format to the result cell.
How WEEKDAY makes the calculation work
Excel stores dates as serial numbers, which lets it add and subtract days with ordinary arithmetic. WEEKDAY returns a number representing the day of the week. In the Monday formula, the second argument, 2, makes Monday equal 1, Tuesday equal 2, and so on through Sunday equal 7. Subtracting that number and adding 1 moves the date back to Monday.
For example, for Thursday, WEEKDAY returns 4 when the return type is 2. Subtracting 4 and adding 1 means subtracting three days, which lands on Monday. If the input itself is Monday, the calculation subtracts one and adds one, leaving the date unchanged. This is why the formula works for every day of the week without an IF statement.
The Sunday formula uses return type 1, where Sunday is 1 and Saturday is 7. For Thursday, the weekday number is 5, so the calculation moves back four days to Sunday. The return-type argument is important: changing it changes the numbering system and therefore the result.
Choose Monday, Sunday, or another starting day
There is no single week-start convention that is right for every workbook. Many business schedules use Monday, while some calendars and reporting systems use Sunday. Use the convention expected by your team, source data, or reporting requirements, and use it consistently in formulas and labels.
- Monday: Use the formula with WEEKDAY return type 2. It is also a convenient choice when working with ISO week numbers.
- Sunday: Use the formula with return type 1. This is common in calendars that display Sunday in the first column.
- A configurable start day: Put a number from 1 to 7 in B1, using Monday as 1 and Sunday as 7. With the date in A2 and the desired start-day number in B1, use
=A2-MOD(WEEKDAY(A2,2)-B1,7).
The configurable version uses the Monday-based numbering of WEEKDAY and calculates how many days to step back to the selected weekday. For example, if B1 contains 3, the chosen first day is Wednesday. Check that B1 contains a whole number from 1 through 7; a text label such as Wednesday will not work as the numeric setting.
Apply the formula to a list of dates
For a table with transaction dates in A2:A100, place the Monday formula in B2 and copy it down. In Excel for Windows, drag the fill handle at the lower-right corner of B2, or select the cells to fill and press Ctrl+D. On Mac, the fill-down shortcut is Command+D. Excel adjusts the reference from A2 to A3, A4, and so on as the formula is copied.
If the dates are in an Excel table, you can add a column named Week Start and use a structured reference. With a table column named Date and the formula entered in that table’s Week Start column, use =[@Date]-WEEKDAY([@Date],2)+1. Excel generally fills the calculated-column formula down the table automatically. The column name in the reference must match the actual table header.
To show the same Monday for the current date rather than a date stored in A2, use =TODAY()-WEEKDAY(TODAY(),2)+1. TODAY updates when the workbook recalculates, so this is useful for a live current-week label but not for a historical record that must remain fixed. For a report you need to preserve, enter or retain a fixed date instead of relying on TODAY.
Format the result as a date
A formula can return the correct date while the cell displays a number such as 46037. That number is not necessarily an error: it is Excel’s underlying date serial. Select the result cells and choose a date format from Home > Number Format. A short date is usually sufficient, while a custom format can show the weekday as well as the date.
To choose a specific display format, select the cells and open Format Cells with Ctrl+1 on Windows or Command+1 on Mac. The Number tab includes date formats, and the Custom category lets you use formats such as d-mmm-yyyy or dddd, mmm d, yyyy. Formatting changes how a value looks, not the date value used in calculations. Avoid converting the result to text if you plan to sort or calculate with it.
Dates that include times or arrive as text
Excel dates can include a fractional part representing the time of day. The basic formula can then return a result with a time fraction, which may be hidden if the cell uses a date-only format. If the source includes times and you need a clean midnight date for grouping, wrap the input in INT. With the date and time in A2, use =INT(A2)-WEEKDAY(INT(A2),2)+1. For a Thursday timestamp, this returns that week’s Monday at midnight.
A date that looks correct but is stored as text may not behave like a date serial. Common signs include left alignment under default formatting or a formula error. Convert the source to actual dates before relying on week calculations. Depending on how the text is structured, Data > Text to Columns can help convert dates; otherwise, use a conversion method appropriate to the source format. Do not assume every text date will be interpreted the same way across regional date settings.
Week start date is not the same as week number
A week-start date identifies an actual calendar date, such as January 12, 2026. A week number is a label, and numbering systems differ. In particular, ISO week numbering starts on Monday and assigns week 1 to the week containing the first Thursday of the year. That means the ISO year for a date near New Year’s Day can differ from its calendar year.
If you need an ISO week number, Excel has the ISOWEEKNUM function, but it answers a different question from the week-start formula. For grouping records, using the actual week-start date is often clearer than grouping only by a week number: week 1 occurs every year, so a number alone cannot identify a unique week. If a report needs both, keep the start date and week number in separate columns.
Common problems and useful checks
- The formula shows a number: Apply a date format; the calculation may already be correct.
- The result is one day off: Check whether you used the Monday or Sunday convention and confirm the WEEKDAY return type.
- The formula returns an error: Check that the source is a valid date and that the formula references the intended cell.
- Excel rejects commas: Some regional settings use semicolons as formula separators. If so, replace the commas with semicolons in the formula syntax accepted by your installation.
- The week appears to start differently in a calendar: Calendar display settings and the formula’s WEEKDAY return type are separate. The formula follows its explicit argument, not necessarily the calendar’s visual layout.
When checking a result, test dates that fall on both the chosen start day and the day immediately before it. A Monday-start calculation should leave a Monday unchanged and assign the preceding Sunday to the Monday six days earlier. These boundary checks quickly reveal whether the intended convention is being applied.
Alternatives for reporting and grouping
The direct WEEKDAY formula is a good fit when you need a reusable date value in a cell. You can then sort by that date, use it in a PivotTable, or group rows by week. A PivotTable can also group date fields by days and set a seven-day interval, but that grouping is a report feature rather than a cell formula, and its start boundary may need adjustment to match the reporting convention.
Another option is to calculate the start date in a helper column and build summaries from that column. This is often easier to audit than embedding week calculations inside a large aggregation formula. It also makes the chosen week boundary visible to colleagues. For a recurring report, write down whether weeks begin Monday or Sunday, especially if data comes from systems that use different conventions.
Frequently Asked Questions
What is the Excel formula for the Monday of a week?
With the date in A2, use =A2-WEEKDAY(A2,2)+1. Format the result cell as a date if Excel displays a serial number.
How do I get the Sunday of the same week?
With the date in A2, use =A2-WEEKDAY(A2,1)+1. This formula treats Sunday as the first day of the week.
Does the formula work when the date is already Monday?
Yes. The Monday formula returns the same date when A2 is a Monday. The equivalent is true for a Sunday date in the Sunday-start formula.
Can I use this formula in Excel for Mac or Excel for the web?
Yes. WEEKDAY and the date arithmetic work in Microsoft 365 for Mac and Excel for the web too. Ribbon placement and some keyboard shortcuts differ by platform, but the formula itself does not.
Why is my week-start result a five-digit number?
Excel is displaying the underlying date serial. Apply a date format to the result cell; changing the format does not alter the calculated date.
Related
- Excel Accounting Format: Show Negative Numbers in Brackets
- Excel Data Analysis: How to Build a Useful Model
- Remove a Data Model from Excel: Steps and Effects
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel TEXT Function Format Options and Examples ExplainedExcel’s TEXT function converts a number, date, or time into text displayed with a format code: use =TEXT(value,…
- Excel VLOOKUP Multiple Values: Formulas That WorkTo return every matching row for one lookup value in Microsoft 365, use FILTER rather than VLOOKUP. With…
- Excel Wildcard Text Search: Formulas and Examples GuideTo check whether text in A2 contains the search term in E2, use =ISNUMBER(SEARCH(E2,A2)). It returns TRUE when…
- Excel MATCH Arguments: What Each One DoesExcel’s MATCH function has three arguments: MATCH(lookup_value, lookup_array, [match_type]). The first two say what to look for and…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- Why Is HLOOKUP Used in Excel? Uses and Examples ExplainedHLOOKUP is used in Excel to find a value in the top row of a table and return…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.