How To Find the Week Start Date in Google Sheets

How to find the week start date in Google Sheets
Reading Time: 4 minutes

Formulas
=TODAY()-WEEKDAY(TODAY(),2)+1
//Returns the week start date for the current date for Monday as the first day of the week.

=TODAY()-WEEKDAY(TODAY())+1
//Returns the week start date with Sunday as the first the day of the week.

=DATE(2022,12,7)-WEEKDAY(DATE(2022,12,7),2)+1
//Returns the week start date for the given date.

If you need to find the first day of any given week, then Google Sheets has a simple method for getting that done. In this guide, we will learn how to find the week start date in Google Sheets. We can do this using different numbering systems — Monday or Sunday as the first day of the week. 

There are situations in which determining the first day of the week can prove helpful. For instance, you might be performing a time-dependent operation in Google Sheets or trying to sort/import data within a specific week and this might require knowing the beginning date of that week. 

There might also be times when you have to schedule an event for a future period and you want to select an ideal week for this operation with a particular day already in mind. Knowing the beginning and the end of such a week could be necessary. 

In either case, there are various ways to go about finding the Week Start Date — manually counting with your fingers included. But Google Sheets as always is there to rescue you from the tedious nature of those approaches. 

We are going to use the WEEKDAY function in combination with two other day-related functions — TODAY() and DATE() functions to find the Week Start Date in Google Sheets. 

Syntax 

=WEEKDAY(date, [type])

date – The given date to find its corresponding day of the week. There are three possible values for this

  • A reference to a cell containing a date
  • A function that returns a date type (we will be utilizing this).
  • A direct value i.e a number.

type –  ( Optional ) – an indication of which numbering system to use for representing weekdays. This is set by default to start counting with Sunday = 1.

  • When type = 1, days are counted from Sunday. Hence, the value of Sunday is 1 and the value of the last day, Saturday, is 7.
  • When type = 2, days are counted from Monday. Hence, the value of Monday is 1 and the value of Sunday is 7.
  • If type = 3, it indicates that numbers 0-6 should be used instead of 1- 7 to represent days. Monday is counted as the first day and the value is 0, hence the value of Sunday is 6.

On carefully observing the syntax of the weekday function in Google Sheets you will notice that its date parameter can take in a function that returns a date. 

Since we can not use the weekday function all by itself to find the week start date in Google Sheets we will combine it with other functions that return date values by supplying them as the date parameter. 

How to find the week start date in Google Sheets 

There are two major situations in which we might need to find the week start date. They are 

  • Finding the week start for the current day
  • Finding the week start date for a specified day other than the current day (it can be a past or future day).

Find the week start date in Google Sheets for the current day 

To find the Week Start Date for the present day we will use the TODAY function in Google Sheets. 

Syntax of the TODAY() function

=TODAY ()

The function simply returns the current day as a date value. 

Step 1: Set up the weekday and today function 

Do the following to set up the two functions: 

  • In a cell of your choice, type in =Weekday… and select the WEEKDAY function from the menu. 
  • Input TODAY() as the first parameter of the function 

Step 2: Specify the numbering system for the weekday function in Google Sheets

We need to specify the numbering system to be used

  • Let’s use Monday as the first day of the week, hence set type to 2.
  • To use Sunday instead, set type to 1 or leave it blank as that is the default value when none is specified.

Enter the formula below into an empty cell

=TODAY()-WEEKDAY(TODAY(),2)+1
Find the week start date in Google Sheets using the current date
Google Sheets first day of the week using the current date

The function returns the Week Start Date as 10/3/2022 which is correct. 

To use Sunday as the first day of the week instead we simply change the formula to the one below 

=TODAY()-WEEKDAY(TODAY())+1

Find the Week Start Date in Google Sheets of a specific date

Another common practice is to find a week start date for a specified day/date. To do this we will replace the TODAY() function with the DATE() function and leave other things the same. 

The DATE() function converts a given day, month, and year into a date. 

Syntax of the DATE function in Google Sheets

=DATE(year, month, day)
  • year – The year component of the date.
  • month – The month component of the date.
  • day – The day component of the date.

Set up the WEEKDAY and DATE functions in Google Sheets by entering the formula below into a cell

=DATE(2022,12,7)-WEEKDAY(DATE(2022,12,7),2)+1

Press Enter.

Find the week start date for a specific date
Google Sheets first day of the week using a specific date

It is easy to find the Week Start Date in Google Sheets, we just need to provide either the current or a specific date. 

See also 

Check out other related articles on our blog:

Tips & Tricks: Working with Date and Time in Google Sheets

How to Use the WEEKDAY Function in Google Sheets 

How to add a Date Picker in Google Sheets