• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

List First Monday Date in Each Month

You are here: Home / Excel Formulas / List First Monday Date in Each Month
list_first_monday_date_in_each_month
January 23, 2020 by Mynda Treacy

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Watch the Video

Subscribe YouTube

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:

list first monday date in each month

*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:

return_type

Click the links below to see comprehensive tutorials for these functions:

DATE function tutorial

WEEKDAY function tutorial

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:

list first monday date in each month formula explained

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:

first day in 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):

last day in month

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 formula with ROW

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:

list first monday date in each month dynamic array formula

No one formula is better than the other, so you’re free to choose which one you use.

list_first_monday_date_in_each_month

More Dynamic Arrays Posts

excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
Excel LET Function

Excel LET Function

Excel LET Function allows you to declare variables and intermediate calculations inside of the formula improving readability and performance.

Excel XLOOKUP Function

The new Excel XLOOKUP Function replaces the need for VLOOKUP, HLOOKUP, INDEX & MATCH and more. It’s available in Office 365.
Excel Hash 2019

Excel Hash 2019 – Dynamic Playoffs Table

Excel Hash Competition 2019 – dynamic playoffs table uses Icons, XOR, Dynamic Arrays and Linked Pictures to create an integrated solution.

Extract Values Present in Two Lists

Use an Excel formula to extract values present in two lists of varying sizes. Watch the video and download the Excel file with examples.
Excel Sorted Dynamic Unique List

Excel Sorted Dynamic Unique List

Excel Sorted Dynamic Unique Lists have never been so easy now that we have dynamic array formulas.
excel dynamic arrays

Excel Dynamic Arrays

The new Excel Dynamic Arrays will change the way you work with Excel formulas. As well as a host of new functions, CTRL+SHIFT+ENTER is no longer required.

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel FormulasTag: Dynamic Arrays
Previous Post:using jitter to avoid over plottingUsing Jitter to Avoid Over Plotting in Power BI
Next Post:Searching for Data With a User Formsearch for data with userform

Reader Interactions

Comments

  1. mike

    March 24, 2023 at 11:06 am

    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

    Reply
    • mike

      March 24, 2023 at 11:14 am

      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

      Reply
    • Mynda Treacy

      March 24, 2023 at 11:15 am

      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)+7

      Or with dynamic arrays:

      =DATE(2020,SEQUENCE(12),7)-WEEKDAY(DATE(2020,SEQUENCE(12),1)-1,15)+7
      Reply
  2. Tango

    December 31, 2022 at 5:36 pm

    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?

    Reply
    • Catalin Bombea

      January 1, 2023 at 3:56 pm

      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)

      Reply
      • Tango

        January 5, 2023 at 1:18 am

        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.

        Reply
        • Catalin Bombea

          January 6, 2023 at 6:21 pm

          Why your first and second week have 8 days each, isn’t that unnatural?

          Reply
          • Tango

            January 7, 2023 at 12:03 pm

            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

            January 10, 2023 at 2:17 pm

            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

          January 10, 2023 at 5:17 pm

          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),””)

          Reply
          • Catalin Bombea

            January 10, 2023 at 6:24 pm

            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

            January 11, 2023 at 11:15 am

            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

            January 11, 2023 at 11:22 pm

            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

  3. Manjit Rakhra

    March 30, 2022 at 5:23 pm

    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…

    Reply
    • Mynda Treacy

      March 30, 2022 at 7:39 pm

      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

      Reply
  4. Manju

    November 7, 2020 at 3:24 pm

    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?

    Reply
    • Mynda Treacy

      November 8, 2020 at 12:16 pm

      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

      Reply
  5. Jim

    May 3, 2020 at 9:14 pm

    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

    Reply
    • Mynda Treacy

      May 4, 2020 at 7:49 pm

      Hi Jim,

      Just add 16 to the formula. The third Wednesday will always be 16 days after the fist Monday in each month.

      Mynda

      Reply
  6. Ganesh

    March 16, 2020 at 7:56 pm

    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 .

    Reply
    • Mynda Treacy

      March 17, 2020 at 3:27 pm

      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

      Reply
  7. Martin

    January 30, 2020 at 10:24 am

    Why the 7th of the month?

    Reply
    • Mynda Treacy

      January 30, 2020 at 6:18 pm

      This ensures that the day returned is the first Monday or later.

      Reply
  8. Tom M

    January 28, 2020 at 11:21 pm

    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.

    Reply
    • Mynda Treacy

      January 29, 2020 at 8:52 am

      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

      Reply
    • jim

      January 30, 2020 at 7:15 pm

      …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)

      Reply
      • jim

        January 30, 2020 at 7:34 pm

        …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)

        Reply
        • jim

          January 30, 2020 at 7:37 pm

          (the year was entered in B1 in my test version)

          Reply
  9. jim

    January 28, 2020 at 9:55 pm

    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)

    Reply
    • Mynda Treacy

      January 29, 2020 at 8:57 am

      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

      Reply
  10. Juan Aguero

    January 28, 2020 at 9:03 pm

    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?

    Reply
    • Mynda Treacy

      January 28, 2020 at 9:09 pm

      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

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.