Table of Contents
To find the first Friday of each month in Excel, you can use the WEEKDAY and DATE functions. The WEEKDAY function is used to determine the day of the week for a given date, while the DATE function is used to calculate a date given a year, month, and day. By combining these two functions, you can easily calculate the first Friday of each month in Excel. To do this, enter the YEAR, MONTH, and DAY into the DATE function to calculate the date of the first day of the month. Then, enter that date into the WEEKDAY function to determine if it is a Friday. If not, use the DATE function to determine the date of the next Friday and then use the WEEKDAY function to confirm it is a Friday.
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.