Find the beginning or get start of week in excel with the simple method suggested in this article. Read more to learn how to do it
Get start of Week in Excel (Sunday or Monday)
It is possible to identify the initial date of the week through operations with Weekday Function. This function has dates as argument and returns a number that correspond to the order of a day within a week.
This tutorial will be divided in 3 parts: Weekday Function, Calculating the Start of the Week as Sunday and as Monday.
Initial Table
Figure 1
1. Weekday Function
This function offers in 2 ways of application:
Figure 2
In the first method, we do not put anything in the return_type argument, and accept the configuration of the Figure 3.
For example, if the result of the hypothetical cell B3 is 4, this date happens in a Wednesday.
Figure 3
In the second, we use configuration offered by the function.
2. Calculating the Start of the Week as Sunday.
2.1 – Computing in 2 Steeps.
Let´s extract the serial number of the date in cell A2.
In cell A5, put the = sign to duplicate the date of cell A2. After, apply the General Format in the Format Tab.
Now, in cell B5 apply the Weekday function, using A2 as first argument and 11 as configuration that establishes Monday as Day 1 and Sunday as Day 7.
To obtain the first day of the week as Sunday, in cell C5, subtract the cell B5 from A5.
Figure 11
Figure 12
The result was given in number, so apply the Date Format use the short-cut CTRL SHIFT 3.
2.2 – Computing in 1 Steep.
In this case, will settle all the operations in on single cell.
In cell A5, put = sign followed by the cell A2 subtracted by Weekday function structured exactly the same way as the last procedure. You may use Figure 16 as support.
The result is the same, however this path is directly and more efficient.
You may change the date in cell A2, the result remains as Sunday.
3. Calculating the Start of the Week as Monday.
In cell A8, put = sign followed by the cell A2 subtracted by Weekday Function. The first argument is the cell A2, however the second argument will receive a different configuration: 3.
This category considers Monday as Day 0 and Sunday, Day 6.
The result is January, 21 2019 which is a Monday.
You may change the date in cell A2, the result remains as Monday.
Final Table
You can also watch our friend video here to learn how to get start of week in excel in his way
Thanks for your attention!