How to list all Mondays / Fridays in a month in Excel?

In certain case, you may need to list or display all Mondays or Fridays of a month or year in a worksheet. Normally, you can list a series of date with Fill Handle, but it can’t help you to fill every Monday or Friday in a month or a year. This article, I will talk about how to list all Mondays or Fridays in a month in Excel.

List all Mondays / Fridays in a month or year with formulas

List all Mondays / Fridays in a month or year with Kutools for Excel

List all Mondays / Fridays in a month or year with formulas

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

The following formulas can help you to list all Mondays or Fridays in a month or year from a given date of the month, please do as follows:

1. Please enter the date that you want to list every Monday or Friday of the month in a cell, for example, I will enter 1/1/2016 in cell A1.

2. Then enter this formula: =IF(WEEKDAY(EOMONTH(A1,-1)+1)=2,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(2,7))-WEEKDAY(EOMONTH(A1,-1)+1)) into cell B1, and press Enter key to get the first Monday of the month. See screenshot:

Note: To get the first Tuesday of the month:

=IF(WEEKDAY(EOMONTH(A1,-1)+1)=3,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(3,7))-WEEKDAY(EOMONTH(A1,-1)+1))

To get the first Wednesday of the month:

=IF(WEEKDAY(EOMONTH(A1,-1)+1)=4,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(4,7))-WEEKDAY(EOMONTH(A1,-1)+1))

To get the first Thursday of the month:

=IF(WEEKDAY(EOMONTH(A1,-1)+1)=5,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(5,7))-WEEKDAY(EOMONTH(A1,-1)+1))

To get the first Friday of the month:

=IF(WEEKDAY(EOMONTH(A1,-1)+1)=6,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(6,7))-WEEKDAY(EOMONTH(A1,-1)+1))

To get the first Saturday of the month:

=IF(WEEKDAY(EOMONTH(A1,-1)+1)=7,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(7,7))-WEEKDAY(EOMONTH(A1,-1)+1))

To get the first Sunday of the month:

=IF(WEEKDAY(EOMONTH(A1,-1)+1)=1,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(1,7))-WEEKDAY(EOMONTH(A1,-1)+1))

3. After displaying the first Monday, please go on entering this formula: =B1+7 to get the second Monday, then drag the fill handle down to list all Mondays in a month or a year as you need, see screenshot:

List all Mondays / Fridays in a month or year with Kutools for Excel

Except the above formulas, here, I can introduce an easy tool-Kutools for Excel, with its Insert Date feature, you can insert all Mondays or Fridays in a month as quickly as you can.

After installing Kutools for Excel, please do as follows:( Go to Download Kutools for Excel Now! )

1. Select a cell to insert the date, and click Kutools > Insert > Insert Date, see screenshot:

2. In the Insert Date dialog box, a month calendar is displayed, you can double click the date to insert it to the cell, you can also change the date format to your needed by checking Using format option to expand the date formatting of the dialog. See screenshot:

Go to Download Kutools for Excel and free trial Now!

Demo: List all Mondays / Fridays in a month or year with Kutools for Excel

Excel Productivity Tools

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

• Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
• Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
• Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
• Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
• 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
• Being used by 110,000 elites and 300+ well-known companies.

Read More Download Now Purchase

---

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

• Increases your productivity by 50% when viewing and editing multiple documents.
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Open and create documents in new tabs of same window, rather than in new windows.
• One second to switch between dozens of open documents!

Read More Download Now Purchase