Table of Contents
To obtain the first day of the week in Excel, the WEEKDAY function can be used. This function returns the numerical value of the day of the week for a given date. By specifying which day of the week is considered the first day (either Sunday or Monday), the formula can be customized to return the desired result. For example, if Monday is considered the first day of the week, entering the formula =WEEKDAY(date,2) will return the numerical value of Monday for the given date. Similarly, entering the formula =WEEKDAY(date,1) will return the numerical value of Sunday as the first day of the week. This function can be useful for organizing and analyzing data based on the days of the week.
Get First Day of Week in Excel (With Examples)
You can use one of the following formulas to get the first day of the week for a date in Excel:
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 Excel:
Example: Get First Day of Week in Excel
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 Number Format dropdown menu on the Home tab and then click Short 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.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I get the first day of the week in Excel? Can you provide some examples?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-get-the-first-day-of-the-week-in-excel-can-you-provide-some-examples/
stats writer. "How can I get the first day of the week in Excel? Can you provide some examples?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-get-the-first-day-of-the-week-in-excel-can-you-provide-some-examples/.
stats writer. "How can I get the first day of the week in Excel? Can you provide some examples?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-get-the-first-day-of-the-week-in-excel-can-you-provide-some-examples/.
stats writer (2024) 'How can I get the first day of the week in Excel? Can you provide some examples?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-get-the-first-day-of-the-week-in-excel-can-you-provide-some-examples/.
[1] stats writer, "How can I get the first day of the week in Excel? Can you provide some examples?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I get the first day of the week in Excel? Can you provide some examples?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.