How to Get First Day of Week in Google Sheets

Understanding Google Sheets Date System: Serial Numbers

Before diving into the formulas, it is essential to grasp how Google Sheets, much like Microsoft Excel, handles dates internally. Dates are not stored as ‘Month/Day/Year’ strings; instead, they are represented as sequential numeric values, often referred to as serial numbers. This system designates a specific historical date (typically January 1, 1900) as day one, with subsequent dates counting upwards. For instance, the number 45000 represents the 45,000th day since the starting date, illustrating the system’s reliance on numerical precision.

This serial number representation is crucial because it allows arithmetic operations to be performed directly on dates. When we add or subtract a number from a date, we are effectively adding or subtracting that many days. Therefore, the core of finding the first day of the week relies entirely on manipulating these underlying numerical representations accurately. Understanding this conversion process is the key to mastering advanced date functions, as the formulas we use below operate purely on these hidden serial numbers.

The Core Logic: Utilizing the MOD Function

The mathematical function central to calculating the start of the week is the MOD function (Modulo). The MOD function returns the remainder after a division operation. In the context of dates, we use 7 (the number of days in a week) as the divisor. The remainder tells us precisely how many days the current date is past the beginning of the 7-day cycle.

To determine the first day of the week, we need to calculate the number of days we must subtract from the current date to reach the official beginning of that weekly period. The standard formulas involve adjusting the date’s serial number before applying the MOD function. This adjustment (subtracting 1 or 2) is necessary to align the numerical day-of-week index with the desired starting day (Sunday or Monday). The result of the MOD calculation gives us the exact offset needed to ‘roll back’ to the start of the week.

Formula 1: Calculating Week Start Based on Sunday (Day 1)

In various regions, notably the United States, Sunday is conventionally treated as the first day of the week. To implement this standard in Google Sheets, we employ a formula designed to calculate the offset needed to return to the nearest Sunday. This method standardizes the date’s weekday number relative to Sunday before finding the remainder, ensuring accuracy within the spreadsheet’s internal numerical clock.

The structure of this formula is highly efficient. It assumes the target date resides in cell A2. We subtract 1 from the date (A2 – 1) before applying the MOD function. This initial subtraction ensures that the resulting weekday number aligns correctly, allowing Sunday to equate to a zero remainder for the modulo operation. The resulting remainder, MOD(A2-1, 7), is the number of days that have elapsed since the previous Sunday. Subtracting this remainder from the original date gives us the precise numeric values corresponding to the first day of the week (Sunday).

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: Calculating Week Start Based on Monday (Day 1)

For organizations following the ISO 8601 standard, which dictates that Monday is the start of the week, a slight modification to the formula is necessary. This approach is widely adopted across business environments and many parts of the globe. Calculating the Monday-based week start requires compensating for the change in the starting index within the formula. Instead of subtracting 1 from the date’s serial number, we subtract 2.

The adjusted MOD function calculation, MOD(A2-2, 7), successfully determines the number of days elapsed since the last Monday. When this result is subtracted from the original date in A2, we isolate the numeric values for the preceding Monday, which represents the first day of that weekly period. Both formulas demonstrate the flexibility of date arithmetic in Google Sheets, allowing users to select the convention that best suits their regulatory or internal reporting requirements.

Formula 2: Get First Day of Week (Assuming First Day is Monday)

=A2-MOD(A2-2, 7)

It is important to reiterate that both of these robust formulas hinge on the assumption that cell A2 contains the original date for which you are trying to determine the week’s beginning.

The following examples show how to use each formula in practice with the following dataset in Google Sheets:

Practical Demonstration: Step-by-Step Application

To fully illustrate the implementation of these calculations, we will apply them to the sample dataset shown above. Our objective is to populate column C with the Sunday-based week start date and column D with the Monday-based week start date, using the source dates listed in column A.

We will enter the respective formulas into the top cells of our output columns. Specifically, we type the Sunday-based formula into cell C2 and the Monday-based formula into cell D2, referencing the date in A2:

• C2: =A2-MOD(A2-1,7) (Sunday Start Convention)
• D2: =A2-MOD(A2-2,7) (Monday Start Convention)

After entering the formulas into the first row, we can efficiently calculate the results for the entire range. We then click and drag each of these formulas down to the remaining cells in columns C and D, leveraging the autofill feature of Google Sheets to instantly calculate the first day of the week for all input dates in Column A:

Formatting the Results: Converting Numeric Values to Dates

By default, the mathematical output generated by these formulas is displayed in its raw form: numeric values. These are the underlying serial numbers we discussed earlier, representing the total number of days since the spreadsheet’s epoch date (1/1/1900). To make this data meaningful for human consumption, we must convert these integers back into a standard calendar date format.

To convert these numeric values to dates, we can highlight the cell range C2:D11 and then utilize the top menu bar. This formatting step is non-destructive; it only changes the display appearance, not the underlying calculated value.

The steps for proper formatting are as follows: highlight the result range, click the Format tab, select Number from the dropdown menu, and then click Date.

Verifying the Output and Calendar Conventions

With the date formatting applied, the results in our spreadsheet become immediately clear. Column C displays the first day of the week for the corresponding date in column A, assuming the week begins on Sunday. Column D provides the same calculation, assuming the week begins on Monday. This differentiation highlights why the choice of formula is critical based on regional or organizational standards.

For instance, we can look at the input date 1/4/2022. Consulting a calendar confirms that this date falls on a Tuesday:

Thus, if we adhere to the standard where Sunday is the first day of the week (Column C), the calculation correctly identifies the week start date as 1/2/2022.

Conversely, if we utilize the ISO standard where Monday is the first day of the week (Column D), the calculation accurately returns the week start date as 1/3/2022.

These calculated dates precisely match the expected calendar structure, validating the effectiveness of the modulo arithmetic approach for finding specific week boundaries.

Conclusion: Mastering Date Segmentation in Google Sheets

The ability to programmatically determine the first day of a week is a fundamental skill for anyone performing sophisticated data segmentation and analysis in Google Sheets. By utilizing simple arithmetic coupled with the powerful MOD function, users can quickly transform a column of various dates into standardized weekly groupings, which is invaluable for tasks such as financial reporting, scheduling, and trend analysis.

Remember that the key differentiator between the two primary formulas lies in the slight offset (subtracting 1 versus subtracting 2) required to correctly align the internal date serial number with the desired start day of the week (Sunday or Monday). Always ensure that you apply the appropriate date formatting after the calculation to correctly display the resulting dates, ensuring clarity and accuracy in all your data processes.