This formula will give next first Tuesday at noon, based on a cutoff of noon
on the 1st Tuesday

=FLOOR(EOMONTH(FLOOR(A1+3.5,7)-4,0)+4,7)+3.5

similarly this will give last Thursday at noon also based on a cutoff at
noon on last Thursday

=FLOOR(EOMONTH(FLOOR(A1+1.5,7)+5,0)+2,7)-1.5

Note: only works correctly with 1900 date system





"Harlan Grove" wrote:

Sean wrote...
Guys a twist on my OP, how could I return a similar value, but this
time the "last" Thursday of each month?

Last Thursday of the month for the date in cell A3 is given by

=A3-WEEKDAY(A3-4,2)+7*(MATCH(TRUE,DAY(A3-WEEKDAY(A3-4,2)
+7*{2;3;4;5;6})<8,0))

Make similar adjustments as in my previous response to use noon as the
cutoff time on that day.

Note: A3-WEEKDAY(A3-n,2) is the previous n_th day of the week before
the date in cell A3, where n_th is in the same sense as WEEKDAY(.,2),
i.e., 1=Monday, 2=Tuesday, etc. Then note that there are at most 5 of
any given weekday in any given month.

daddylonglegs wrote...
This formula will give next first Tuesday at noon, based on a cutoff
of noon on the 1st Tuesday

=FLOOR(EOMONTH(FLOOR(A1+3.5,7)-4,0)+4,7)+3.5

similarly this will give last Thursday at noon also based on a
cutoff at noon on last Thursday

=FLOOR(EOMONTH(FLOOR(A1+1.5,7)+5,0)+2,7)-1.5

Note: only works correctly with 1900 date system
....

Note also that in Excel 2003 and prior it requires loading the
Analysis ToolPak add-in, since EOMONTH isn't a built-in function in
those older versions.

Thanks Harlan