Table of Contents
The formula to find the first Friday of each month in Excel is “=DATE(YEAR(A1),MONTH(A1),1)+MOD(7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+5”. This formula calculates the date of the first day of a given month, and then adds the necessary number of days to reach the first Friday of that month. It can be used in Excel to efficiently and accurately determine the first Friday of any given month.
Excel: Find the First Friday of Each Month
You can use the following formula in Excel to find the first Friday of a given month:
=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+2)
This particular example returns the first Friday of the month for the date in cell A2.
The following example shows how to use this formula in practice.
Example: Find First Friday of Month in Excel
Suppose we have the following list of dates in Excel:
We can type the following formula into cell B2 to find the first Friday of the month for the date in cell A2:
=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+2)
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the date of the first Friday of each month in column A.
For example, the first date of 1/1/2023 contains the month of January for 2023.
We can confirm that 1/6/2023 is indeed the first Friday of this month by referring to a calendar:
Similarly, the next date of 2/1/2023 contains the month of February for 2023.
We can confirm that 2/3/2023 is indeed the first Friday of this month by referring to a calendar:
We can confirm that each date in column B indeed represents the first Friday of the month for each corresponding date in column A.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: