Excel Date Formula

[jesshope2023]

Hi!

I am trying (with difficulties, apparently) to create the below described formula for my work. I am somewhat of a novice with excel, and would truly appreciate the help!!!

I need this formula to return the date that is at least 2 weeks from today that falls on either the first, third, or fifth Tuesday of the month (whichever is closest after the two weeks) AND that will exclude federal holidays.

Thank you in advance!!

 

[Dave Patton]

A few examples would be very useful.

T202306a.xlsm
	A	B	C	D
1	Days Future	14		Unique List
2	1-Jun-23	Tue 20-Jun-23		Tue 06-Jun-23
3	20-Jun-23	Tue 04-Jul-23		Tue 20-Jun-23
4	5-Jul-23	Tue 01-Aug-23		Tue 04-Jul-23
5	1-Aug-23	Tue 15-Aug-23		Tue 18-Jul-23
6				Tue 01-Aug-23
7				Tue 15-Aug-23
8				Tue 29-Aug-23
9				Tue 05-Sep-23
10				Tue 19-Sep-23
11
3c

Cell Formulas
Range	Formula
B2:B3	B2	=XLOOKUP(WORKDAY.INTL(A2+$B$1-1,1,"1011111",Holidays),$D$2:$D$10,$D$2:$D$10,,1)
B4	B4	=XLOOKUP(WORKDAY.INTL(A4+14,1,"1011111",Holidays),$D$2:$D$10,$D$2:$D$10,,1)
B5	B5	=XLOOKUP(WORKDAY.INTL(A5+13,1,"1011111",Holidays),$D$2:$D$10,$D$2:$D$10,,1)

Named Ranges
Name	Refers To	Cells
Holidays	='3c'!$M$1	B2:B5

 

[Dave Patton]

T202306a.xlsm
	A	B
9	1-Jun-23	20-Jun-23
10	20-Jun-23	4-Jul-23
11	5-Jul-23	1-Aug-23
12	1-Aug-23	5-Sep-23
13
3c

Cell Formulas
Range	Formula
B9:B12	B9	=LET(dt,WORKDAY.INTL(F2-1,{1,3,5},"1011111",Holidays),XLOOKUP(WORKDAY.INTL(A2+$B$1-1,1,"1011111",Holidays),dt,dt,,1))

Named Ranges
Name	Refers To	Cells
Holidays	='3c'!$M$1	B9:B12

 

[kvsrinivasamurthy]

UDF solution is here.
Code for UDF "GetTuesday"

Function GetTuesday(Ip As Long)
Dim Dt&, T&, temp
Dim M
Dt = Ip + 15
M = Evaluate("=" & Dt & "-WEEKDAY(" & Dt & ",12)+{1,8,15,22,29,36,43,50}")
For T = 1 To 5
If Day(M(T)) < 9 Or (Day(M(T)) > 14 And Day(M(T)) < 22) Or Day(M(T)) > 28 Then 'and day(M(T))<22)
temp = M(T): Exit For
End If
Next T
ActiveCell.NumberFormat = "d/m/yy"
GetTuesday = temp
End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
Save file as .xlsm

DATE	RESULT
01-05-2023​	16-05-2023​
02-05-2023​	16-05-2023​
08-05-2023​	30-05-2023​
14-05-2023​	30-05-2023​
20-05-2023​	30-05-2023​
26-05-2023​	06-06-2023​
01-06-2023​	20-06-2023​
07-06-2023​	20-06-2023​
13-06-2023​	04-07-2023​
19-06-2023​	04-07-2023​
25-06-2023​	04-07-2023​
01-07-2023​	18-07-2023​
07-07-2023​	18-07-2023​
13-07-2023​	01-08-2023​
19-07-2023​	01-08-2023​
25-07-2023​	08-08-2023​

 

[Dave Patton]

What date do you want to show if the date calculated is on a Federal Holiday?
My post #3 was not the correct post; please ignore it.