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

In this article, we will discuss how to list all dates which fall on Mondays or Fridays in the given month in Excel. Like numeric data, Excel also works well with dates. If we want to fill a series of dates in a worksheet, we can simply use Fill handle. But what if we want only those dates that fall on a Monday or Friday. This task cannot be achieved using Fill handle. We can use two ways to fill the dates in the given month that fall on Monday or Friday. The user defined function would be used in the example 1. The Kutools tab are utilized in the example 2.

Example 1: Using formula

Let us look at how a formula can be used to list all dates falling on either a Monday or a Friday in the given month.

Step 1

Enter the first date of the month for which you want to list all Mondays or Fridays. Let us take an example date say 1st May 2023 that we have entered in cell A1 as shown.

Step 2

Enter the given formula in the adjacent cell. That is, if the date is entered in cell A1, then enter the below formula in cell B1.

The formula is as follows.

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

Step 3

After entering the formula, press Enter key. The first date that falls on Monday will be filled in cell B1.

Note− After pressing Enter key if cell B1 is filled with a random number, then please set the format of the cell to Date. After setting to Date, the first Monday of the month will appear in cell B1.

Step 4

To get the successive dates for Mondays, enter the given formula and drag downwards using the Fill handle to get all the dates in the given month falling on Monday.

The formula is as follows.

=B1 + 7

Step 5

All Mondays are listed in column B, then in cell C1, enter the given formula to get the first Friday of the month.

The formula is as follows.

=CEILING(EOMONTH(A1,-1)-5,7)+6

Step 6

After entering the formula, press Enter key. The first date that falls on Friday will be filled in C1.

Note − After pressing Enter key if the cell C1 is filled with a random number, then please set the format of the cell to Date. After setting to Date, the first Monday of the month will appear in cell C1.

Step 7

To get the successive dates for Fridays, enter the given formula and drag downwards using the Fill handle to get all the dates in the given month falling on Friday.

The formula is as follows.

=C1 + 7

As seen from the output image, all the dates falling on Mondays are displayed in column B and all the dates falling on Fridays are displayed in column C.

Example 2: Using Kutools

Kutools is an add-on tool that needs to be installed separately. Once Kutools is installed, it is automatically integrated into Excel.

Step 1

Click on Kutools tab.

Step 2

Under the Editing heading, click on the Insert option. 

                               

Step 3

Select the Insert Date option from the list.

Step 4

A calendar box appears on the screen with the calendar showing the current month.

Step 5

As mentioned in the instructions written on the box, double-click on all dates that fall on Mondays.

Step 6

After this, double click on all dates that fall on Fridays.

                     

Step 7

Select the desired format of the date from the given formats. And then click on the Cancel button.

Step 8

All the dates in the current month falling on Mondays or Fridays will be displayed on the currently open worksheet in Excel.

As seen from the output image, the date format shows the respective weekday along with the date.

Conclusion

In this article, we have seen two different methods to list all the dates in the given month that fall on either Mondays or Fridays. Before implementing the first approach that uses a formula, it is necessary that the user must write the syntax and argument of the formula in the proper way to obtain an accurate result. Otherwise, inaccurate results may occur. While it is required that Kutools should be installed beforehand before using the second method, it is equally important that the reader has a good understanding of Excel.