Working with dates and weeks is an essential skill for any Excel user. Whether you’re working in accounting or putting together a planner in Excel, knowing how to get the first day of the week in Excel is a valuable skill. In this tutorial, you’ll learn how to get the first day of the week in Excel.
You’ll learn two different methods to get the first day of the week: using the WEEKDAY() and MOD() functions. Similarly, you’ll learn how to calculate the week start date using different start dates. Because how you measure dates depends on where you live and the industry you work in, you’ll learn how to customize what the first day of the week is.
By the end of this tutorial, you’ll have learned the following:
- How to get the first day of the week in Excel
- How to customize the week start day when finding the first day of the week
Get Monday of the Week in Excel Using WEEKDAY()
How can you get the Monday of a date in Excel?
To get the Monday of the week (or, rather, the week start) is to use the WEEKDAY() function. Assuming your date is in cell A2, you can use =A2 – WEEKDAY(A2, 3).
Before diving further, let’s take a look at how the WEEKDAY() function works in Excel:
WEEKDAY(serial_number,[return_type])We can see that the function accepts two parameters:
serial_number=is the date that we’re calculating the weekday forreturn_type=is an optional parameter that allows you to customize the start date of the week
When we use a return_type=3, Excel interprets this to mean that the week starts on Monday. If a date falls on a Monday, Excel will return a value of 0.
Let’s see how we can use what we just learned to get the beginning of the week using Excel with a step by step example.
Get Monday of the Week in Excel Using WEEKDAY()
Load Your Dataset in Excel
Load your dataset in Excel. In the example above, we’re using two columns. The first has the dates that we want to get the week start date for. The second contains a placeholder for us to store the Monday of the week.
Use the WEEKDAY() Function to Get the First Day of the Week
Use the WEEKDAY() function to calculate the first day of the week. In order to get the Monday of the week we can use the following formula in cell B2: A2-WEEKDAY(A2, 3). This subtracts the number of days following the Monday from the date, giving you the week’s Monday.
Hit the Return Key to Find the Monday of the Week
When you hit the Return key, the function evaluates and returns the first Monday of the week. In our example above, this is Monday April 17, 2023.
Use the Fill Handle to Get the Week Start for Every Cell
Select the fill handle (the cross hair at the bottom right of the cell) and drag it down to the end of the data. This calculates the formula for each cell and returns the Monday start of the date’s week.
In the example we used the Excel WEEKDAY() function to get the first day of the week. We assumed that the week starts on Mondays, but that’s not always the case! Let’s take a look at how you can get the week’s start date in Excel using a Sunday start.
Get the First Day of the Week in Excel Using WEEKDAY() with Sunday Start
In some cases, your week may not actually start on a Monday, but rather on a Sunday. In these cases, we can use a similar approach but need to modify our function slightly. In the example above, we used the value of 3 which returned a Monday. In order to use a Sunday start, we can use the value of 2. This would result in the formula looking like this:
=A2-WEEKDAY(A2, 2)Let’s take a look at a hands-on example in order see how this works.
Step 1: Load your dataset
In the example above, we’re using the same dataset as before. The data has dates from April 2023 through October 2023.
Step 2: Use the WEEKDAY() Function to Get the Week’s Sunday
In order to get the Sunday of the week of a given date in Excel, we can use the formula: A2-WEEKDAY(A2, 2). This will subtract any days beyond the Sunday and result in the Sunday of the week.
Step 3: Use the Fill Handle to Get the Sunday for Every Cell
Once the formula is filled in, you can click on the fill handle and drag it all the way down. When you let go, Excel will copy the function all the way down, returning the Sunday of the week for each date.
Get the First Day of the Week in Excel Using MOD()
Similarly, we can use the Excel MOD() function to calculate the first day of the week for a given date. The MOD() function returns the remainder of dividing a value by another value. In order to calculate the first day of the week assuming a Monday start using the MOD() function, you can use the function below:
=A2-MOD(A2-2, 7)Let’s now take a look at a hands-on example of how to use the function:
Step 1: Load your dataset
We’re using the same dataset again to calculate the first day of the week. This dataset has six different dates and placeholders to calculate the Monday of that week.
Step 2: Use the MOD() Function to Get the Week’s Monday
By using the MOD() function, we’re determining what number of days to subtract from our current date. Because Excel stores dates as numbers, this division is possible.
Step 3: Use the Fill Handle to Get the Monday for Every Cell
When you drag down the fill handle, you’re able to calculate the week start date for each date in the column. When you let go, Excel copies the formula down all the way.
Conclusion
In this tutorial, you learned how to calculate the week start date for a given date in Excel. Being able to do this allows you to group values by a given week and to be able to determine when a given week starts. You first learned how to calculate the first day of the week using the WEEKDAY() function, both for weeks that begin with either a Monday or a Sunday. Then, you learned an alternative method, using the MOD() function to calculate the week start of a given day.
Additional Resources
To learn more about related topics, check out the tutorials below: