# 3 weekdays or more in a week relates to same month

#### atearth

##### New Member
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.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### DanteAmor

##### Well-known Member
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))))))

Last edited:

#### DanteAmor

##### Well-known Member
O run this macro.
Assume that the data starts in cell A1

Code:
``````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

##### Board Regular
Or try:

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

Last edited:

#### Phuoc

##### Board Regular
Edit:

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