# Formula for yielding 1st and 3rd Sunday of every month and every Friday.

#### andreascostas

##### Board Regular
I need a formula to find the dates for the 1st and 3rd Sunday of every month, and a formula for finding
the date for every Friday for every month.
So I can input the month in A1 and get the results in another cell.
Thank You

#### Phuoc

##### Board Regular
The first Sunday: =WORKDAY.INTL(DATE(2019,\$A\$1,0),1,"1111110")

The third Sunday: =WORKDAY.INTL(DATE(2019,\$A\$1,0),3,"1111110")

#### tyija1995

##### Well-known Member
I need a formula to find the dates for the 1st and 3rd Sunday of every month, and a formula for finding
the date for every Friday for every month.
So I can input the month in A1 and get the results in another cell.
Thank You
How exactly are you inputting the month in A1, just by putting "Jan", "Feb", "Mar" etc?

#### sandy666

##### Well-known Member
are you able to use PowerQuery aka Get&Transform?

 Month Month Day Name Date Month Day Name Date March March Friday 01/03/2019​ March Sunday 03/03/2019​ March Friday 08/03/2019​ March Sunday 17/03/2019​ March Friday 15/03/2019​ March Friday 22/03/2019​ March Friday 29/03/2019​

or

 Month Day Name Date September Friday 06/09/2019​ Friday 13/09/2019​ Friday 20/09/2019​ Friday 27/09/2019​ Sunday 01/09/2019​ Sunday 15/09/2019​

Last edited:

#### tyija1995

##### Well-known Member
 A B C D E F 1 January 04/01/2019 11/01/2019 18/01/2019 25/01/2019 2 February 01/02/2019 08/02/2019 15/02/2019 22/02/2019 3 Mar 01/03/2019 08/03/2019 15/03/2019 22/03/2019 29/03/2019

<tbody>
</tbody>

With the following formula set up in Cells B1 (Drag DOWN column B) and a formula in C1 (Drag DOWN AND ACROSS columns C:F)

B1:
Code:
``IF(DAY(WORKDAY.INTL(DATE(2019,MONTH("1/"&\$A1),1),1,"1111011"))=8,DATE(2019,MONTH("1/"&\$A1),1),WORKDAY.INTL(DATE(2019,MONTH("1/"&\$A1),1),1,"1111011"))``
C1:
Code:
``IF(B1+7>EOMONTH(DATE(2019,MONTH("1/"&\$A1),1),0),"",B1+7)``
The formula in B1 assumes you are working with the year 2019.

In a similar fashion for 1st and 3rd sunday of a given month try this in cells H1 & I1 (for the month in A1)

H1:
Code:
``IF(DAY(WORKDAY.INTL(DATE(2019,MONTH("1/"&\$A1),1),1,"1111110"))=8,DATE(2019,MONTH("1/"&\$A1),1),WORKDAY.INTL(DATE(2019,MONTH("1/"&\$A1),1),1,"1111110"))``
I1:
Code:
``=H1+14``

Last edited:

#### andreascostas

##### Board Regular
I input "Jan" "Feb" etc.

#### Phuoc

##### Board Regular
I input "Jan" "Feb" etc.
Edit the formula in post #2 :

The first Sunday:=WORKDAY.INTL((1&A1)-1,1,"1111110")

Last edited:

1,081,984
Messages
5,362,554
Members
400,679
Latest member
alecalec202

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...