3 weekdays or more in a week relates to same month

[atearth]

If a week has 3 or more weekdays in it, the first the same month is shown in the next cell? What formula can I use to show this?

Example dates
31-Jan-17 would show 01-Feb-17.
31-Jan-18 would show 01-Jan-18.
01-Sep-21 would show 01-Sep-21.
01-Sep-22 would show 01-Aug-22.

 

[DanteAmor]

Came out a very extensive formula

=IF(WEEKDAY(A1,2)=1,IF(AND(MONTH(A1+1)=MONTH(A1),MONTH(A1+2)=MONTH(A1)),A1-DAY(A1)+1,EOMONTH(A1,0)+1),
IF(WEEKDAY(A1,2)=2,IF(OR(AND(MONTH(A1-1)=MONTH(A1),MONTH(A1+1)=MONTH(A1)),AND(MONTH(A1+1)=MONTH(A1),MONTH(A1+2)=MONTH(A1))),A1-DAY(A1)+1,EOMONTH(A1,0)+1),
IF(WEEKDAY(A1,2)=3,IF(OR(AND(MONTH(A1-1)=MONTH(A1),MONTH(A1+1)=MONTH(A1)),AND(MONTH(A1+1)=MONTH(A1),MONTH(A1+2)=MONTH(A1)),AND(MONTH(A1-1)=MONTH(A1),MONTH(A1-2)=MONTH(A1))),A1-DAY(A1)+1,EOMONTH(A1,0)+1),
IF(WEEKDAY(A1,2)=4,IF(OR(AND(MONTH(A1-1)=MONTH(A1),MONTH(A1+1)=MONTH(A1)),AND(MONTH(A1-2)=MONTH(A1),MONTH(A1-1)=MONTH(A1))),A1-DAY(A1)+1,EOMONTH(A1,0)+1),
IF(WEEKDAY(A1,2)=5,IF(AND(MONTH(A1-1)=MONTH(A1),MONTH(A1-2)=MONTH(A1)),A1-DAY(A1)+1,EOMONTH(A1,0)+1))))))

 

[DanteAmor]

O run this macro.
Assume that the data starts in cell A1

Sub Macro12()
    With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = _
        "=IF(WEEKDAY(RC[-1],2)=1,IF(AND(MONTH(RC[-1]+1)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]+2)=MONTH(RC[-1])),RC[-1]-DAY(RC[-1])+1,EOMONTH(RC[-1],0)+1)," & Chr(10) & _
        " IF(WEEKDAY(RC[-1],2)=2,IF(OR(AND(MONTH(RC[-1]-1)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]+1)=MONTH(RC[-1])),AND(MONTH(RC[-1]+1)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]+2)=MONTH(RC[-1]))),RC[-1]-DAY(RC[-1])+1,EOMONTH(RC[-1],0)+1)," & Chr(10) & _
        " IF(WEEKDAY(RC[-1],2)=3,IF(OR(AND(MONTH(RC[-1]-1)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]+1)=MONTH(RC[-1])),AND(MONTH(RC[-1]+1)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]+2)=MONTH(RC[-1])),AND(MONTH(RC[-1]-1)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]-2)=MONTH(RC[-1]))),RC[-1]-DAY(RC[-1])+1,EOMONTH(RC[-1],0)+1)," & Chr(10) & _
        " IF(WEEKDAY(RC[-1],2)=4,IF(OR(AND(MONTH(RC[-1]-1)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]+1)=MONTH(RC[-1])),AND(MONTH(RC[-1]-2)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]-1)=MONTH(RC[-1]))),RC[-1]-DAY(RC[-1])+1,EOMONTH(RC[-1],0)+1)," & Chr(10) & _
        " IF(WEEKDAY(RC[-1],2)=5,IF(AND(MONTH(RC[-1]-1)=MONTH(RC[-1])," & _
        "MONTH(RC[-1]-2)=MONTH(RC[-1])),RC[-1]-DAY(RC[-1])+1,EOMONTH(RC[-1],0)+1))))))"
        .Value = .Value
    End With
End Sub

 

[Phuoc]

Or try:

=DATE(YEAR(A1),MONTH(A1-WEEKDAY(A1)+4),1)

 

[Phuoc]

Edit:

=EOMONTH(A1-WEEKDAY(A1)+4,-1)+1