How to get week start and end date based on a specific date in Excel?
If you frequently work with schedules, project planning, attendance records, or timesheets, you may often need to determine the starting and ending dates of the week for a given date. For instance, if you have a list of dates, you might need to quickly find out which Monday (week start) and Sunday (week end) each date falls within, as demonstrated in the screenshot. This can help with grouping, reporting, and summarizing weekly data. But how can you efficiently get this information in Excel? This article introduces several practical methods—from simple formulas to more advanced automated techniques—to extract week start and end dates in Excel.
Get week start date and end date based on specific date with formulas
This approach is suitable if you have a straightforward list of dates and want to quickly find out the week start and end dates for each using formulas. It is efficient for small to medium-sized data sets, doesn't require any setup, and works in all modern versions of Excel.
Here, the simple formulas below help you calculate both the week's starting Monday and ending Sunday for any given date. Please follow these steps:
Get week start date from given date:
1. If your list of dates is in column A, click on a cell (for example, C2) where you want to display the week start (Monday) date.
2. Enter the following formula into that cell:
=A2-WEEKDAY(A2,2)+13. Press Enter to confirm. Then drag the fill handle down to apply the formula for as many rows as necessary.
The result will display the Monday of the week for each corresponding date.
Get week end date from given date:
1. Click in the cell where you want to display the week end (Sunday) date, for example, D2.
2. Enter the following formula:
=A2+7-WEEKDAY(A2,2)3. Again, press Enter, then use the fill handle to copy the formula down for the other dates.
Each result will show the Sunday belonging to the same week as the date in column A.
Tips and notes:
- These formulas assume your week starts on Monday and ends on Sunday (European convention). If your workweek is different, you may need to adjust the
WEEKDAYfunction’s second parameter. - Incorrect results can occur if dates are not recognized by Excel as valid date serial numbers (for instance, if imported as text). Make sure your dates are formatted properly.
- If you copy results to another sheet, ensure that cell references adjust correctly or use absolute references if needed.
- You can easily format the resulting columns as Dates using Home > Number Format > Short Date to ensure date display consistency.
VBA Code - Automate extraction of week start and end dates for multiple date lists
This method is ideal if you need to repeatedly extract week start and end dates for different ranges, or want to automate the process, including handling user-selected date lists. VBA is well-suited for more advanced users and those automating repetitive tasks across many sheets.
1. Click Developer Tools > Visual Basic, which will open the Microsoft Visual Basic for Applications editor. From there, click Insert > Module and input the following code into the window:
Sub ExtractWeekStartEndDates()
Dim WorkRng As Range
Dim cell As Range
Dim ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the date range to extract week start/end dates:", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
Set ws = WorkRng.Worksheet
ws.Cells(1, WorkRng.Columns(1).Column + WorkRng.Columns.Count).Value = "Week Start (Mon)"
ws.Cells(1, WorkRng.Columns(1).Column + WorkRng.Columns.Count + 1).Value = "Week End (Sun)"
For Each cell In WorkRng
If IsDate(cell.Value) Then
cell.Offset(0, WorkRng.Columns.Count).Value = cell.Value - Weekday(cell.Value, 2) + 1
cell.Offset(0, WorkRng.Columns.Count + 1).Value = cell.Value + 7 - Weekday(cell.Value, 2)
End If
Next
Application.DisplayAlerts = True
End Sub2. After pasting the VBA code, click the 
Run button. A dialog will appear allowing you to choose the date range in your worksheet. The macro will add two columns to the right of your selection, labeling them "Week Start (Mon)" and "Week End (Sun)", and automatically populate them for each date in your list.
Parameters and notes:
- The macro works for any rectangular selection with date values (e.g., a column or block of dates).
- If any cell in your chosen range is not a valid Excel date, the week start/end cells will be left blank for that row.
- You can modify the header labels "Week Start (Mon)" and "Week End (Sun)" as needed by editing the code.
- To run the macro again, simply repeat the selection and execution steps.
- VBA operations cannot be undone using Ctrl+Z, so consider making a copy of your data beforehand.
Troubleshooting: If you don’t see the Developer tab, go to File > Options > Customize Ribbon and enable Developer. If an error occurs on execution, double-check the selection includes date values and that macros are enabled in your Excel application.
Use Power Query to add week start/end columns to imported date data
For users managing very large datasets, especially those importing data from external files or databases, Power Query (Get & Transform) offers a robust, repeatable way to automatically calculate and add week start and end columns. Power Query is available in all recent Excel versions and is especially suited for cleaning and reshaping data before further analysis.
- Select your data table (make sure it contains a column of dates), then click Data > From Table/Range to launch Power Query Editor.
- In Power Query, select your date column. With it highlighted, go to the Add Column tab and click Date > Week > Start of Week. This will add a new column showing each date's Monday equivalent.
Tip: By default, the start of week is Monday. If your data uses a different week start day, click the drop-down in Start of Week for other options. - With the date column still selected, click Add Column > Date > Week > End of Week. This adds a Sunday date for each row.
- After verifying your new columns, click Home > Close & Load to return the transformed data (now including week start/end) into your workbook.
Benefits and notes:
- Power Query makes it easy to refresh calculations automatically if your source data changes.
- This method is optimal for regularly updated, imported, or very large lists due to its automation and reproducibility.
- If the Start of Week/End of Week buttons are grayed out, ensure your column is detected as type "Date", which you can adjust using the "Data Type" dropdown in Power Query.
- Power Query does not modify your original source data but instead creates a new output table with the week calculations included.
Summary suggestions: Choose the method best suited for your scenario: simple formulas for smaller ad hoc lists; VBA for automation or advanced customization; and Power Query for repeatable workflows and large datasets. Test on sample data before deploying solutions broadly and remember to save your work before performing operations that change your workbook structure.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in