With Excel formulas we can easily list the first Monday date in each month, or the last Sunday, or whatever day you like.
I’ll use the DATE and WEEKDAY functions to generate the list of dates, and then I’ll show you some alternatives, including a dynamic array solution using SEQUENCE. Plus, I’ll connect the formula to a data validation list that lets you choose which day to display.
Download Workbook
Enter your email address below to download the sample workbook.
Watch the Video
List First Monday Date in Each Month Formula
The formula is:
=DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},7)- WEEKDAY(DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3)
And you can see the results in Office 365* spill to the cells below, generating a list of 12 dates:
*Note: Excel 2019 and earlier users must select 12 empty cells first, then enter the formula with CTRL+SHIFT+ENTER. The spill functionality is currently only available in Office 365 for users on the Insider Channel.
I’ll dive into explaining the formula in a moment, but first the syntax for DATE and WEEKDAY are below:
=DATE(year, month, day)
=WEEKDAY(date, return_type)
Where return_type is selected from this list:
Click the links below to see comprehensive tutorials for these functions:
List First Monday Date in Each Month Formula Explained
We can break the formula down into 3 parts which I’ve colour coded below:
=DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},7)- WEEKDAY( DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1 ,3)
Part 1: Generates a list of dates for the 7th of each month. The month argument contains an array of values 1 to 12, one for each month.
Part 2: Generates a list of dates for the 1st of each month minus 1 day. i.e. the last day of the previous month.
Part 3: Finds the WEEKDAY number of the last day of the previous month (from part 2).
Finally, part 3, the weekday number is subtracted from part 1 to return the first Monday date for each month.
The image below shows the formula broken down into steps:
Choose First Day of Each Month to Display
With some data validation we can allow the user to choose which day they want listed by altering the number of days to subtract from the last day of the month:
Choose Last Day of Each Month to Display
Alternatively, with the EOMONTH function we can modify the formula to list the last day of the month. It requires an adjustment to the number of days we need to subtract (see table in blue):
List First Monday Date in Each Month Formula with ROW
Instead of listing the days of the months manually inside the DATE formula i.e. {1;2;3;4;5;6;7;8;9;10;11;12}, we can use the ROW function to automatically generate them:
List First Monday Date in Each Month Dynamic Array Formula
Alternatively, for those of us with Office 365 and dynamic arrays we can use the SEQUENCE function to generate the 12 month numbers:
No one formula is better than the other, so you’re free to choose which one you use.
Using Jitter to Avoid Over Plotting in Power BI
mike
I only needed the second thursday of each month. So, I rearranged the formula to give me the first thursday of each month, then added 7 to the result in a second column and hid the first.
Nothing else I looked at was quite as simple or straightforward. Thanx
mike
First Thursday DATE: (7), WEEKDAY: (-4,3) (COLUMN B2)
=DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},7)- WEEKDAY(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1)-4,3)
COLUMN C2) =B2+7
Simple
Mynda Treacy
You can do it all in one formula like this:
=DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},7)-WEEKDAY(DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},1)-C50,15)+7Or with dynamic arrays:
Tango
Hi Mynda,
If I want to add some public holidays, if somehow, for example if the first day of the Monday hit one of the holiday, the value of it should add 7 days. Is it possible to do it?
Catalin Bombea
Hi Tango,
You should setup a lookup table with holidays.
I suggest using a second column to check the generated date:
The formula should be:
=A1+IF(ISNUMBER(MATCH(A1,LookupTable[Dates],0)),7,0) (Add 7 days if generated date is in lookup table)
Tango
Thank you so much on your advise Catalin. It is very helpful.
Indeed, I have another question. If I see the first week of the month differently. How could I change the formula to achieve this?
For example, in Feb 2023, the first Monday of Feb should be 6/Feb/2023. But I want to see this as the second week, and the first week of Feb only have Wednesday to Sunday. So an empty string will return if I ask for the first Monday of Feb 2023. Plus, if I ask for the third Monday of Feb 2023, it should return me 13 Feb 2023.
I want this algorithm as for every month and every year. So the first week of a month could be the last week of the previous month.
Sun Mon Tue Wed Thu Fri Sat Sun
First week-> 29 30 31 1 2 3 4 5
Second Week> 6 7 8 9 10 11 12 13
So the above month don’t have first Monday and Tuesday.
Catalin Bombea
Why your first and second week have 8 days each, isn’t that unnatural?
Tango
My Bad, its a typo, but you got the idea if you look at the calendar of Feb 23.
The first week of Feb, is the last week of Jan too.
Catalin Bombea
I think you’ll have to check if the week number of the generated date is the same with the week number of the first day of the month:
=IF(WEEKNUM(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1))=WEEKNUM(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},7)-WEEKDAY(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3)),DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},7)-WEEKDAY(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3),"")
Tango
Thank you Catalin, your formula below can find the first week of those months (2/1, 1/5 and 2/10 only). Indeed, I want the second Monday of each months with the same algorithm. If I add +7 with your formula below. It will only return me 9/1 , 8/5 and 9/10 only. All others month became and empty cell. Please advise how to solve it?
=IF(WEEKNUM(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1))=WEEKNUM(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},7)-WEEKDAY(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3)),DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},7)-WEEKDAY(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3),””)
Catalin Bombea
You can’t use the same formula to get the first, second or third monday, you have to adjust it:
=IF(WEEKNUM(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},14))=WEEKNUM(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},14)-WEEKDAY(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3)),DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},14)-WEEKDAY(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3),"")
Tango
I am terribly sorry. First of all, thank you for your prompt reply. Indeed, the formula in your last message didn’t quite achieve I am looking for. It return me (9/1/2023,13/2/2023,13/3/2023,
10/4/2023, (Empty cell of May), 12/6/2023, 10/7/2023, 14/8/2023, 11/9/2023, 9/10/2023
13/11/2023 and 11/12/2023)
I was actually hoping to get (9/1/23,6/2/23, 6/3/23, 3/4/23,8/5/23,5/6/23,3/7/23,7/8/23,4/9/23, 9/10/23,6/11/23 and 4/12/23) instead.
Please kindly help and look forward to your super save.
Catalin Bombea
Hard to understand what you want and why.
Basically, you want a mix of first monday and second monday, in the same formula? Based on what criteria?
Nothing seems consistent in what you describe as expected result:
The rule of “find the first monday after first day of the month” applies to most months, the only exceptions are: January, May and October. For these 3 exceptions the date you indicated is the second after the first day of the month.
January 1, and October 1, according to the way you want to “see” the calendar, is part of the last week of previous month and also part of the first week in current month. The date you indicated for Jan and Oct are part of the third week of the current month, it does not align with what you describe, for other months the date is part of week 2.
Please clarify and provide a clear rule like the one I mentioned (“find the first monday after first day of the month”), or better, open a topic on our forum, there you’ll be able to attach files to clarify your request. Once we understand what you’re trying to do, we might suggest alternative solutions.
Cheers,
Catalin
Manjit Rakhra
Hi Mynda, have a query….In the First Day of Each Month to Display. There is a table for Data Validation….how is the corresponding no decided for e.g for – Monday is 1…Tue- 2 Wed- 3….but in the last day of the month data validation table…Monday is 7, Tuesday is 1…
Mynda Treacy
Hi Manjit,
Depending on what day of the week the first/last day falls on, the numbers in the table should be subtracted accordingly to get the desired outcome. It’s decided because these numbers return the correct result when plugged into the formulas. It might help to look at the file and work through the formula. I hope I’ve helped, but let me know if you still have questions.
Mynda
Manju
Hi Mynda
Thanks for your support.
I wanted to capture last Sunday, date for every month as an Array. For that I used following formula;
“=DATE(A1,{1;2;3;4;5;6;7;8;9;10;11;12},31) – WEEKDAY(DATE(A1,{1;2;3;4;5;6;7;8;9;10;11;12},25)-7,3)”
I use year as a parameter through the A1 cell. This formula is valid for 2020 & 2021. But when I use 2022 as the year it’s not give output properly. Means, for the month April last Sunday appears as May 1st. What should I do?
Mynda Treacy
Hi Manju,
In the file download there is the formula for the last Sunday in each month, which is this:
=EOMONTH(DATE(2022,{1;2;3;4;5;6;7;8;9;10;11;12},1),0)- WEEKDAY(EOMONTH(DATE(2022,{1;2;3;4;5;6;7;8;9;10;11;12},1),0)-C70,3)Mynda
Jim
Mynda,
I tried tweaking this to give me that 3rd Wednesday in each month. Can’t seem to get it right. Any suggestions?
Thanks, Jim
Mynda Treacy
Hi Jim,
Just add 16 to the formula. The third Wednesday will always be 16 days after the fist Monday in each month.
Mynda
Ganesh
If I want to calculate ,
I am giving money on 1 Jan and I should collect month on month Emi in any date of first 2 weeks only.
I want frequency of 28 days or 35 days for 24 months.
Week should be same.. disbursement date +28 days will come on 29 Jan. So should go for next week and so on .
Mynda Treacy
Hi Ganesh,
Sorry, your question isn’t quite clear, sorry. Please post your question on our Excel forum where you can also upload a sample file.
Mynda
Martin
Why the 7th of the month?
Mynda Treacy
This ensures that the day returned is the first Monday or later.
Tom M
how would you be able to display every Monday of the year? Is that possible?
I thought =DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},{7;14;21;28})-WEEKDAY(DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},{1;8;15;22;27})-1,3) but it only fills out the month of January.
Mynda Treacy
Hi Tom,
I would just use AutoFill > Series to create a list of Monday dates in a year. A formula would be too convoluted.
Mynda
jim
…or, if you want to use a Dynamic Array, you could use:
=DATE(year,1,)-WEEKDAY(DATE(year,1,),3)+SEQUENCE(52,,7,7)
which could have some tinkering to allow for 53-Monday years
jim (loving playing with the Dynamics)
jim
…and that tinkering could be:
=DATE(B1,1,)-WEEKDAY(DATE(B1,1,),3)+SEQUENCE(52+(WEEKDAY(DATE(B1,1,),3)+(MOD(B1,4)=0)>5),,7,7)
which adds an extra week to the SEQUENCE when 1st Jan is a Monday, with the MOD() part to allow for leap years
jim (roll on XLOOKUP)
jim
(the year was entered in B1 in my test version)
jim
hi Mynda,
isn’t the last day of the previous month (part 2 above) always going to be the same day of the week as the 7th day of the current one (part 1)?
makes no real difference to the formulae except that you can cut and re-use the same function within them
I’ve always counted forward from day 0, giving (I think) the (very slightly) neater-looking:
=DATE(2020,ROW(1:12),)-WEEKDAY(DATE(2020,ROW(1:12),),3)+7
jim (not an Insider but now delighted to have dynamic arrays)
Mynda Treacy
Hi Jim,
Great alternative, thanks for sharing.
Dynamic Arrays went generally available just after I wrote this post 🙂 Glad you have them. Keep your eye out for XLOOKUP as it’ll be coming next.
Mynda
Juan Aguero
Thank you very much Mynda for this excellent tutorial, the logic behind each formula is impressive!
I have a question, why is number 3 used as the type of data for WEEKDAY function?
Mynda Treacy
Thanks, Juan! 3 is used because this returns a 0 value for Monday. The other numbers return 1 or higher. You could use a different number but then you’d have to also adjust the value to subtract.
Mynda