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
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.
Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
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 need by checking Using format option to activate the date formatting list box. 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
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!







