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

[andreascostas]

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]

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]

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]

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​

 

[tyija1995]

	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:

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:

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:

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:

=H1+14

 

[andreascostas]

I input "Jan" "Feb" etc.

 

[Phuoc]

I input "Jan" "Feb" etc.

Edit the formula in post #2 :

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