In this “Data to Decisions” blog, we’ll look at how to use Excel functions to generate the first Sunday of the first week of each month—an essential skill for anyone organizing weekly events, managing schedules, or preparing work rosters.
Utilizing the WEEKDAY function in Excel, we can backtrack from the first day of the month to find when the first Sunday occurs. Read along to know how this is achieved easily.
Before we dive in with the topic, please read our dedicated blog on generating the starting dates of every month.
For 2024, the formula for generating starting dates will be
=DATE(2024,SEQUENCE(12,1,1,1),1)We’ve generated a SEQUENCE of 12 rows starting from 1 to 12 (for the 12 months) and used the DATE function to generate the 1st day.
Use the TEXT function to quickly get the corresponding weekday of the first dates in 2024:
=TEXT(C6, “ddd”)
With this canvas, let us extract the first Sunday of first week of every month.
Step 01:
The WEEKDAY function is the key to unlocking this task. It returns a number representing the day of the week for a given date, syntax for this is:
In our example, the formula in cell E6 would be:
=WEEKDAY(C6,1)A return type of 1 for WEEKDAY means Sun=1, Mon=2, and so on
This shows us that January starts on the 2nd day of the week, February on the 5th day, and so on as shown below,
Let us quickly cross-check this with a calendar:
Step 02:
From the above weekday calculation, we can see that, in January we need to go back about 1 day to get the first Sunday, and in February it’s 4 days to get to the first Sunday.
That is from our weekday calculation, we need to move back one day.
In F6 our formula will be:
=E6-1For all the months, this would be:
Click and drag the formula from the first cell till the last needed cell (here, F17 to populate the formulas in those cells.
Step 03:
Now, we subtract the first day from the “go back by” days to get the first Sunday of every month. In cell G6 our formula is,
=C6-F6Again, by using the TEXT function, we can check our results:
This method ensures that if the first of the month is already a Sunday, it remains unchanged; otherwise, it subtracts the necessary number of days to go back to the previous Sunday.
Using a single formula:
We can get the same result by using a single formula by subtracting the first day of the month (in cell C6) from the weekday of the same minus 1. Our formula in cell I6 will be:
=C6-(WEEKDAY(C6,1)-1)
Check our step-by-step tutorial video for generating the first Sunday of every month:
If you have any feedback or suggestions, please post them in the comments below.
