Find the Start Day of a Week … For Any Date

Finding the Start of a Week (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

 

Thanks for your attention!

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>