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 would 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 in Google Sheets for the present day we will use the TODAY function in Google Sheets along with the WEEKDAY function.
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:
- Enter the TODAY function.
- In the same cell, enter the WEEKDAY function separating the two functions with a minus(-).
- Input TODAY() as the first parameter of the WEEKDAY 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.
- Then enter +1 to the formula.
The complete formula looks as given below.
=TODAY()-WEEKDAY(TODAY(),2)+1
The function returns the Week Start Date as 10/3/2022 which is the beginning week day for the current date, ie, 10/6/2022.
To use Sunday as the first day of the week instead we simply change the second parameter in the WEEKDAY function to 0 or leave it empty, which will set it to the default value, ie, 0 for Sunday.
The formula for the week start date for the current day with Sunday as the first day of the week is as given below.
=TODAY()-WEEKDAY(TODAY())+1Find 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. The formula to find the week start date in Google Sheets for a specific date is as given below.
=DATE(2022,12,7)-WEEKDAY(DATE(2022,12,7),2)+1The above formula will return the week start date for the date specified in the formula.
Press Enter to get the week start date in Google Sheets.
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