Table of Contents
Google Sheets is an incredibly powerful and versatile tool for working with data. One of the most useful features of Google Sheets is the ability to quickly and easily get the first day of the week for any date. In this tutorial, we’ll show you how to do just that. We’ll walk you through the step-by-step process of finding the first day of the week using Google Sheets, as well as provide helpful tips and tricks to get the most out of this powerful program. So if you’d like to learn how to get the first day of the week in Google Sheets, read on!
You can use one of the following formulas to get the first day of the week for a date in Google Sheets:
Formula 1: Get First Day of Week (Assuming First Day is Sunday)
=A2-MOD(A2-1, 7)
Formula 2: Get First Day of Week (Assuming First Day is Monday)
=A2-MOD(A2-2, 7)
Both formulas assume that cell A2 contains the date you’d like to find the first day of the week for.
The following examples show how to use each formula in practice with the following dataset in Google Sheets:
Example: Get First Day of Week in Google Sheets
We can type the following formulas into cell C2 and D2 to get the first day of the week for the date in cell A2:
- C2: =A2-MOD(A2-1,7)
- D2: =A2-MOD(A2-2,7)
We can then click and drag each of these formulas down to the remaining cells in columns C and D:
By default, the date values are shown as numeric values.
Note: The numeric values represent the number of days since 1/1/1900.
To convert these numeric values to dates, we can highlight the cell range C2:D11 and then click the Format tab, then click Number from the dropdown menu and then click Date:
Column C shows the first day of the week that the date in column A falls in, assuming the first day is considered Sunday.
Column D shows the first day of the week that the date in column A falls in, assuming the first day is considered Monday.
For example, we can look at a calendar to see that 1/4/2022 is on a Tuesday:
Thus, if we consider Sunday the first day of the week then that date is 1/2/2022.
Or if we consider Monday the first of the week then that date is 1/3/2022.
These dates match the ones calculated by our formulas.