Table of Contents
Excel has a built-in function called “WEEKDAY” which can be used to determine the day of the week for a given date. By combining this function with the “DATE” function, we can create a formula that will calculate the date of the first Sunday of every month. First, we specify the month and year for which we want to find the first Sunday. Then, we use the “DATE” function to create a date for the first day of that month. Next, we use the “WEEKDAY” function to determine the day of the week for that date. Finally, we subtract this day from 1 and add 1 to it, resulting in the first Sunday of the month. This formula can be copied and used for all the other months, making it easy to locate the first Sunday of every month in Excel.
You can use the following formula in Excel to find the first Sunday of a given month:
=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2))
This particular example returns the first Sunday of the month for the date in cell A2.
The following example shows how to use this formula in practice.
Example: Find First Sunday of Month in Excel
Suppose we have the following list of dates in Excel:
We can type the following formula into cell B2 to find the first Sunday of the month for the date in cell A2:
=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2))
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the date of the first Sunday of each month in column A.
For example, the first date of 1/1/2023 contains the month of January for 2023.
We can confirm that 1/1/2023 is indeed the first Sunday of this month by referring to a calendar:
Similarly, the next date of 2/1/2023 contains the month of February for 2023.
We can confirm that 2/5/2023 is indeed the first Sunday of this month by referring to a calendar:
We can confirm that each date in column B indeed represents the first Sunday of the month for each corresponding date in column A.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: