Formula choosing the 2nd or 4th Monday.

T

TarkTeam4

New Member
Joined
Sep 11, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Can anyone come up with ONE formula that with choose the 2nd OR 4th Monday, depending on a particular "Start Date".

For example...
If the start date falls between Tuesday, July 26, 2022 - Sunday August 7th, 2022, the formula would pick Monday, August 8, 2022 (2nd Monday of the month).
However, if the start date falls between Tuesday, August 9, 2022 - Sunday, August 21, 2022, the SAME formula would pick Monday, August 22, 2022 (4th Monday of month).
Then again, if the start date falls between Tuesday, August 23, 2022 - Sunday, September 11, 2022, the formula would pick Monday, September 12, 2022 (2nd Monday of the month), and so forth and so forth....

In my many attempts, I kept getting "the next Monday,
and not the 2nd or 4th Monday. :cautious:
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
How about this LAMBDA function?

COMPSTAT Template1
BCDEF
3LAMBDA
48/8/2022
5
6
7StartEndDateResult
87/26/20228/7/20227/30/20228/8/2022
98/9/20228/21/20228/14/20228/22/2022
Data
Cell Formulas
RangeFormula
B4B4=LAMBDA(year,month,day_index,nth_day,WORKDAY.INTL(DATE(year,month,0),nth_day,REPLACE("111111",day_index,0,"0")))(2022,8,1,2)
F8:F9F8=NTHDAYINMONTH(YEAR(D8),8,1,IF(AND(D8>=$B$8,D8<=$C$8),2,IF(AND(D8>=$B$9,D8<=$C$9),4,"NA")))
 
Upvote 0
lrobbo314 said:
How about this LAMBDA function?

COMPSTAT Template1
BCDEF
3LAMBDA
48/8/2022
5
6
7StartEndDateResult
87/26/20228/7/20227/30/20228/8/2022
98/9/20228/21/20228/14/20228/22/2022
Data
Cell Formulas
RangeFormula
B4B4=LAMBDA(year,month,day_index,nth_day,WORKDAY.INTL(DATE(year,month,0),nth_day,REPLACE("111111",day_index,0,"0")))(2022,8,1,2)
F8:F9F8=NTHDAYINMONTH(YEAR(D8),8,1,IF(AND(D8>=$B$8,D8<=$C$8),2,IF(AND(D8>=$B$9,D8<=$C$9),4,"NA")))
Click to expand...
I need the formula to grab it's information from one cell with the start date .(any given date within a range), and not from several cells that lays out the range.
 
Upvote 0
Like this?

COMPSTAT Template1
DEF
7DateResult
87/30/20228/8/2022
98/14/20228/22/2022
Data
Cell Formulas
RangeFormula
F8:F9F8=NTHDAYINMONTH(YEAR(D8),8,1,IF(AND(D8>=DATE(2022,7,26),D8<=DATE(2022,8,7)),2,IF(AND(D8>=DATE(2022,8,9),D8<=DATE(2022,8,21)),4,"NA")))
 
Upvote 0
MrExcelPlayground12.xlsx
AB
2Tuesday, July 26, 2022Monday, August 8, 2022
3Wednesday, July 27, 2022Monday, August 8, 2022
4Thursday, July 28, 2022Monday, August 8, 2022
5Friday, July 29, 2022Monday, August 8, 2022
6Saturday, July 30, 2022Monday, August 8, 2022
7Sunday, July 31, 2022Monday, August 8, 2022
8Monday, August 1, 2022Monday, August 8, 2022
9Tuesday, August 2, 2022Monday, August 8, 2022
10Wednesday, August 3, 2022Monday, August 8, 2022
11Thursday, August 4, 2022Monday, August 8, 2022
12Friday, August 5, 2022Monday, August 8, 2022
13Saturday, August 6, 2022Monday, August 8, 2022
14Sunday, August 7, 2022Monday, August 8, 2022
15Monday, August 8, 2022Monday, August 8, 2022
16Tuesday, August 9, 2022Monday, August 22, 2022
17Wednesday, August 10, 2022Monday, August 22, 2022
18Thursday, August 11, 2022Monday, August 22, 2022
19Friday, August 12, 2022Monday, August 22, 2022
20Saturday, August 13, 2022Monday, August 22, 2022
21Sunday, August 14, 2022Monday, August 22, 2022
22Monday, August 15, 2022Monday, August 22, 2022
23Tuesday, August 16, 2022Monday, August 22, 2022
24Wednesday, August 17, 2022Monday, August 22, 2022
25Thursday, August 18, 2022Monday, August 22, 2022
26Friday, August 19, 2022Monday, August 22, 2022
27Saturday, August 20, 2022Monday, August 22, 2022
28Sunday, August 21, 2022Monday, August 22, 2022
29Monday, August 22, 2022Monday, August 22, 2022
30Tuesday, August 23, 2022Monday, September 12, 2022
31Wednesday, August 24, 2022Monday, September 12, 2022
32Thursday, August 25, 2022Monday, September 12, 2022
33Friday, August 26, 2022Monday, September 12, 2022
34Saturday, August 27, 2022Monday, September 12, 2022
35Sunday, August 28, 2022Monday, September 12, 2022
Sheet6
Cell Formulas
RangeFormula
B2:B35B2=LET(a,A2+SEQUENCE(30,1,0),b,FILTER(a,WEEKDAY(a,2)=1),c,FILTER(b,(DAY(b)>7)*(DAY(b)<15)),d,FILTER(b,(DAY(b)>21)*(DAY(b)<29)),MIN(IFERROR(c,999999),IFERROR(d,999999)))
A3:A35A3=A2+1
 
Upvote 0
Solution
lrobbo314 said:
Like this?

COMPSTAT Template1
DEF
7DateResult
87/30/20228/8/2022
98/14/20228/22/2022
Data
Cell Formulas
RangeFormula
F8:F9F8=NTHDAYINMONTH(YEAR(D8),8,1,IF(AND(D8>=DATE(2022,7,26),D8<=DATE(2022,8,7)),2,IF(AND(D8>=DATE(2022,8,9),D8<=DATE(2022,8,21)),4,"NA")))
Click to expand...
It seems when trying to apply your this formula I am getting #NAME?
I tried several time and it's not computing on my end.
JamesCanale said:
MrExcelPlayground12.xlsx
AB
2Tuesday, July 26, 2022Monday, August 8, 2022
3Wednesday, July 27, 2022Monday, August 8, 2022
4Thursday, July 28, 2022Monday, August 8, 2022
5Friday, July 29, 2022Monday, August 8, 2022
6Saturday, July 30, 2022Monday, August 8, 2022
7Sunday, July 31, 2022Monday, August 8, 2022
8Monday, August 1, 2022Monday, August 8, 2022
9Tuesday, August 2, 2022Monday, August 8, 2022
10Wednesday, August 3, 2022Monday, August 8, 2022
11Thursday, August 4, 2022Monday, August 8, 2022
12Friday, August 5, 2022Monday, August 8, 2022
13Saturday, August 6, 2022Monday, August 8, 2022
14Sunday, August 7, 2022Monday, August 8, 2022
15Monday, August 8, 2022Monday, August 8, 2022
16Tuesday, August 9, 2022Monday, August 22, 2022
17Wednesday, August 10, 2022Monday, August 22, 2022
18Thursday, August 11, 2022Monday, August 22, 2022
19Friday, August 12, 2022Monday, August 22, 2022
20Saturday, August 13, 2022Monday, August 22, 2022
21Sunday, August 14, 2022Monday, August 22, 2022
22Monday, August 15, 2022Monday, August 22, 2022
23Tuesday, August 16, 2022Monday, August 22, 2022
24Wednesday, August 17, 2022Monday, August 22, 2022
25Thursday, August 18, 2022Monday, August 22, 2022
26Friday, August 19, 2022Monday, August 22, 2022
27Saturday, August 20, 2022Monday, August 22, 2022
28Sunday, August 21, 2022Monday, August 22, 2022
29Monday, August 22, 2022Monday, August 22, 2022
30Tuesday, August 23, 2022Monday, September 12, 2022
31Wednesday, August 24, 2022Monday, September 12, 2022
32Thursday, August 25, 2022Monday, September 12, 2022
33Friday, August 26, 2022Monday, September 12, 2022
34Saturday, August 27, 2022Monday, September 12, 2022
35Sunday, August 28, 2022Monday, September 12, 2022
Sheet6
Cell Formulas
RangeFormula
B2:B35B2=LET(a,A2+SEQUENCE(30,1,0),b,FILTER(a,WEEKDAY(a,2)=1),c,FILTER(b,(DAY(b)>7)*(DAY(b)<15)),d,FILTER(b,(DAY(b)>21)*(DAY(b)<29)),MIN(IFERROR(c,999999),IFERROR(d,999999)))
A3:A35A3=A2+1
Click to expand...
This one worked perfectly. Thank you.
 
Upvote 0
T202209a.xlsm
AB
1
2
3Start Date
4Sun 31-Jul-22Mon 08-Aug-22
5Wed 10-Aug-22Mon 22-Aug-22
6
6a
Cell Formulas
RangeFormula
B4:B5B4=IF(AND(A4>44768,A4<44780),WORKDAY.INTL(EOMONTH(44768,0),2,"0111111"),IF(AND(A4>44782,A4<44794),WORKDAY.INTL(EOMONTH(A4,-1),4,"0111111")))
 
Upvote 0
N.B. The Vlookup formula does not account for the gaps between ranges.

T202209a.xlsm
ABCDEF
1
2Tue 26-Jul-22Sun 07-Aug-22Mon 08-Aug-22
3Start DateTue 09-Aug-22Sun 21-Aug-22Mon 22-Aug-22
4Sun 31-Jul-22Mon 08-Aug-22Tue 23-Aug-22Sun 11-Sep-22Mon 05-Sep-22
5Wed 10-Aug-22Mon 22-Aug-22
6
7Sun 31-Jul-22Mon 08-Aug-22
8Wed 10-Aug-22Mon 22-Aug-22
6a
Cell Formulas
RangeFormula
F2,F4F2=WORKDAY.INTL(D2,2,"0111111")
F3F3=WORKDAY.INTL(EOMONTH(D3,-1),4,"0111111")
B4:B5B4=IF(AND(A4>44768,A4<44780),WORKDAY.INTL(EOMONTH(44768,0),2,"0111111"),IF(AND(A4>44782,A4<44794),WORKDAY.INTL(EOMONTH(A4,-1),4,"0111111")))
B7:B8B7=VLOOKUP(A7,$D$2:$F$4,3,1)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
460
Jaxi
J
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
983
felixstraube
F
R
  • Solved
Counting months
Replies
3
Views
357
JamesCanale
JamesCanale
T
  • Solved
Depending on Start date / formula for 1st Monday of month (of 1st FULL week)
Replies
9
Views
843
Dave Patton
D
T
  • Solved
Assistance with array formula
Replies
2
Views
321
tigerzen
T

Forum statistics

Threads
1,214,920
Messages
6,122,272
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top