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.

After installing Kutools for Excel, please do as follows:

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!