Last Friday and first Friday of month in google sheets

In this tutorial you will learn how to get the first friday of month in google sheets. Also you will see how to get last friday as well.

To get the first friday of month in google sheets, you can use the EOMONTH formula with the WEEKDAY formula.

4. Write the formula:  =IF(WEEKDAY(EOMONTH(A2,-1)+1)=6,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1)<7,EOMONTH(A2,-1)+1+(6-(WEEKDAY(EOMONTH(A2,-1)+1))),EOMONTH(A2,-1)+1+6))

Here is an animation showing this in action:

Explanation of the Formula:

Here is how the formula works.

=IF(WEEKDAY(EOMONTH(A2,-1)+1)=6,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1)<7,EOMONTH(A2,-1)+1+(6-(WEEKDAY(EOMONTH(A2,-1)+1))),EOMONTH(A2,-1)+1+6))

Note: Weekday starts from Sunday which is 1 and ends with Saturday which is 7.

We are using the IF formula. First we are checking if the first day of the month is a Friday. Here 6 is for Friday.

If the first date of the month is a Friday, then output that date. 

Else, check if the first date of month is less than Friday, if yes then add (6-current day) to get the first Friday. Else if the first date of month is greater than Friday (i.e Saturday) then add 6 to get the first friday of the month.

Example – First friday of month in google sheets

We have a list of dates in column A. We want to know the first Friday of the current month for each date.

For first friday of month in google sheets, use the below formula:

=IF(WEEKDAY(EOMONTH(A2,-1)+1)=6,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1)<7,EOMONTH(A2,-1)+1+(6-(WEEKDAY(EOMONTH(A2,-1)+1))),EOMONTH(A2,-1)+1+6))

To get the first Friday of next month, you have to pass 1 in the EOMONTH formula and for the previous month you will pass -1.

Here is the formula for first Friday on next month in google sheets:

=IF(WEEKDAY(EOMONTH(A2,0)+1)=6,EOMONTH(A2,0)+1,IF(WEEKDAY(EOMONTH(A2,0)+1)<7,EOMONTH(A2,0)+1+(6-(WEEKDAY(EOMONTH(A2,0)+1))),EOMONTH(A2,0)+1+6))

Here is for previous month:

=IF(WEEKDAY(EOMONTH(A2,-2)+1)=6,EOMONTH(A2,-2)+1,IF(WEEKDAY(EOMONTH(A2,-2)+1)<7,EOMONTH(A2,-2)+1+(6-(WEEKDAY(EOMONTH(A2,-2)+1))),EOMONTH(A2,-2)+1+6))

You can see all the formulas in action in below sheet:

Last Friday of current month in google sheets

=IF(WEEKDAY(EOMONTH(A2,0))=6,EOMONTH(A2,0),IF(WEEKDAY(EOMONTH(A2,0))<7,EOMONTH(A2,0)-(1+(WEEKDAY(EOMONTH(A2,0)))),EOMONTH(A2,0)-1))

Example:-

Below we have a list of dates in column A. For each of the dates, we want to find the last Friday of the current month, previous month and next month.

The formulas are below:

Last friday of current month:

=IF(WEEKDAY(EOMONTH(A2,0))=6,EOMONTH(A2,0),IF(WEEKDAY(EOMONTH(A2,0))<7,EOMONTH(A2,0)-(1+(WEEKDAY(EOMONTH(A2,0)))),EOMONTH(A2,0)-1))

Last friday of last month:

=IF(WEEKDAY(EOMONTH(A2,-1))=6,EOMONTH(A2,-1),IF(WEEKDAY(EOMONTH(A2,-1))<7,EOMONTH(A2,-1)-(1+(WEEKDAY(EOMONTH(A2,-1)))),EOMONTH(A2,-1)-1))

Last friday of next month:

=IF(WEEKDAY(EOMONTH(A2,1))=6,EOMONTH(A2,1),IF(WEEKDAY(EOMONTH(A2,1))<7,EOMONTH(A2,1)-(1+(WEEKDAY(EOMONTH(A2,1)))),EOMONTH(A2,1)-1))

Here is a screenshot showing all the 3 formulas in action:

Wrapping up

In this tutorial, you learned how to get the first friday of month in google sheets. Also you learned how to get the last friday of the month. You saw how we calculated the same for the previous month and next month.

Now go ahead and use these formulas in your own worksheet.

Appendix

[1] EOMONTH formula in google sheets – Link
[2] WEEKDAY formula in google sheets – Link

Further Reading

New to google sheets ? Start here

More related to Formulas:

Learn more about Google sheets Formulas.
Logical functions in google sheets
Error handling in google sheets