3 weekdays or more in a week relates to same month

atearth

New Member
Joined
Feb 25, 2015
Messages
27
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.
 

Some videos you may like

Excel Facts

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,950
Office Version
2007
Platform
Windows
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
Joined
Dec 3, 2018
Messages
11,950
Office Version
2007
Platform
Windows
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
Joined
Apr 29, 2016
Messages
193
Or try:

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

Watch MrExcel Video

Forum statistics

Threads
1,100,198
Messages
5,473,106
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top