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?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Month[/td][td][/td][td=bgcolor:#70AD47]Month[/td][td=bgcolor:#70AD47]Day Name[/td][td=bgcolor:#70AD47]Date[/td][td][/td][td=bgcolor:#70AD47]Month[/td][td=bgcolor:#70AD47]Day Name[/td][td=bgcolor:#70AD47]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]March[/td][td][/td][td=bgcolor:#E2EFDA]March[/td][td=bgcolor:#E2EFDA]Friday[/td][td=bgcolor:#E2EFDA]

01/03/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]March[/td][td=bgcolor:#E2EFDA]Sunday[/td][td=bgcolor:#E2EFDA]

03/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]March[/td][td]Friday[/td][td]

08/03/2019​

[/td][td][/td][td]March[/td][td]Sunday[/td][td]

17/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]March[/td][td=bgcolor:#E2EFDA]Friday[/td][td=bgcolor:#E2EFDA]

15/03/2019​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]March[/td][td]Friday[/td][td]

22/03/2019​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]March[/td][td=bgcolor:#E2EFDA]Friday[/td][td=bgcolor:#E2EFDA]

29/03/2019​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Month[/td][td][/td][td=bgcolor:#70AD47]Day Name[/td][td=bgcolor:#70AD47]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]September[/td][td][/td][td=bgcolor:#E2EFDA]Friday[/td][td=bgcolor:#E2EFDA]

06/09/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Friday[/td][td]

13/09/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]Friday[/td][td=bgcolor:#E2EFDA]

20/09/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Friday[/td][td]

27/09/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]Sunday[/td][td=bgcolor:#E2EFDA]

01/09/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Sunday[/td][td]

15/09/2019​

[/td][/tr]
[/table]

 

[tyija1995]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]January[/TD]
[TD]04/01/2019[/TD]
[TD]11/01/2019[/TD]
[TD]18/01/2019[/TD]
[TD]25/01/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]February[/TD]
[TD]01/02/2019[/TD]
[TD]08/02/2019[/TD]
[TD]15/02/2019[/TD]
[TD]22/02/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mar[/TD]
[TD]01/03/2019[/TD]
[TD]08/03/2019[/TD]
[TD]15/03/2019[/TD]
[TD]22/03/2019[/TD]
[TD]29/03/2019[/TD]
[/TR]
</tbody>[/TABLE]

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")