Next 1st or 3rd Thursday, whichever comes first from a given date

[davidcom]

Hi all. Could someone please help me with a formula that will give me the next 1st or 3rd Thursday, whichever comes first, from a given date.

So for example if A1 = 4/2/24, the formula will return 4/4/24 (which is the earlier of the next 1st or 3rd Thursday)

If A1 = 4/11/24, the formula will return 4/18/24 (which is the 3rd Thursday of April, so the earlier of the next 1st or 3rd Thursday from 4/11/24)

And if A1 = 4/18/24, the formula would return 5/2/24 (which is the 1st Thursday of May, so the earlier of the next 1st or 3rd Thursday from 4/18/24)

Hopefully that makes sense!!

 

[Cubist]

Try:

=LET(t,A1 + (5 - WEEKDAY(A1, 2) + IF(WEEKDAY(A1, 2) > 5, 7, 0))-1,IF(A1<t,t,t+14))

 

[davidcom]

Thanks @Cubist . Unfortunately, that formula didn't work. When I entered 4/11/2024 into A1, the result should be 4/18/24 (which is the 3rd Thursday of April, so the earlier of the next 1st or 3rd Thursday from 4/11/24). But your formula returned 4/25/24 (the 4th Thursday of April)

 

[Cubist]

Book1
	A	B
1	4/2/24	4/4/24
2	4/11/24	4/18/24
3	4/18/24	5/2/24
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(t,DATE(YEAR(A1),MONTH(A1),1+MOD(11-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7))+1,IF(A1<t,t,IF(A1<t+14,t+14,t+28)))

 

[Tetra201]

See if the following formula works for you:

=LET(a,TOROW(WORKDAY.INTL(EOMONTH(A1,{-1;0}),{1,3},"1110111")),XLOOKUP(A1+1,a,a,,1))

 

[bebo021999]

Book1
	A	B
1	02-Apr	04-Apr
2	11-Apr	18-Apr
3	19-Apr	02-May
4	01-May	02-May
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=DATE(YEAR(A1),MONTH(A1),IF(DAY(A1)<=1+CHOOSE(WEEKDAY(A1-DAY(A1)+1),4,3,2,1,0,6,5),1+CHOOSE(WEEKDAY(A1-DAY(A1)+1),4,3,2,1,0,6,5),IF(DAY(A1)<=1+CHOOSE(WEEKDAY(A1-DAY(A1)+1),4,3,2,1,0,6,5)+14,1+CHOOSE(WEEKDAY(A1-DAY(A1)+1),4,3,2,1,0,6,5)+14,DAY(EOMONTH(A1,0))+1+CHOOSE(WEEKDAY(EOMONTH(A1,0)+1),4,3,2,1,0,6,5))))
A4	A4	=EOMONTH(A3,0)+1

 

[bebo021999]

With Workday

Book1
	A	B
1	02-Apr	04-Apr
2	11-Apr	18-Apr
3	19-Apr	02-May
4	01-May	02-May
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=IF(A1<=WORKDAY.INTL(A1-DAY(A1)+1,1,"1110111"),WORKDAY.INTL(A1-DAY(A1)+1,1,"1110111"),IF(A1<=WORKDAY.INTL(A1-DAY(A1)+1,3,"1110111"),WORKDAY.INTL(A1-DAY(A1)+1,3,"1110111"),WORKDAY.INTL(EOMONTH(A1,0)+1,1,"1110111")))

 

[davidcom]

Book1
	A	B
1	4/2/24	4/4/24
2	4/11/24	4/18/24
3	4/18/24	5/2/24
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(t,DATE(YEAR(A1),MONTH(A1),1+MOD(11-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7))+1,IF(A1<t,t,IF(A1<t+14,t+14,t+28)))

Thanks @Cubist . This did the trick!

@Tetra201 your formula worked as well. Thanks!

 

[Tetra201]

@davidcom: Please note that the formulas from Post #4 and Post #5 will return different results for 5/17/24.
Post #4: 5/30/24 (5th Thursday of May)
Post #5: 6/6/24 (1st Thursday of June)

 

[davidcom]

@davidcom: Please note that the formulas from Post #4 and Post #5 will return different results for 5/17/24.
Post #4: 5/30/24 (5th Thursday of May)
Post #5: 6/6/24 (1st Thursday of Jun