Need formula to find 5th Tuesdays

[Maune]

Hello everyone!

I need some help! I am trying to set up a recurring appointment in Outlook that falls on months with 5 Tuesdays. You can't select that option for recurring dates, but you can import custom dates from an excel spreadsheet.

I need to be able to enter a formula that will look forward and list the dates that are 5th Tuesdays, and I cant seem to figure it out. I thought maybe the workdays function might be involved, but I am really at a loss.

Is this even possible to do?

Thank you so much for any help!

 

[Rick Rothstein]

Not sure if this layout would be useful to you or not, but assuming the year is in cell A1, put this formula in cell B1 and copy it across to cell M1...

=IF(MONTH(DATE($A1,COLUMN(A1),36)-WEEKDAY(DATE($A1,COLUMN(A1),5)))=COLUMN(A1),DATE($A1,COLUMN(A1),36)-WEEKDAY(DATE($A1,COLUMN(A1),5)),"")

The dates with 5 Tuesdays in them will be listed under the column for that month (Column B = January, Column C = February, etc.).

 

[jasonb75]

My best effort, fill down as required, this will return up to 4 years worth of dates, hopefully that will be enough.

=IFERROR(SMALL(IF(WEEKDAY(ROW(OFFSET($A$1,TODAY(),,1461)))=3,IF(DAY(ROW(OFFSET($A$1,TODAY(),,1461)))>28,ROW(OFFSET($A$1,TODAY(),,1461)))),ROWS($A$1:$A1)),"")

Array confirmed with Shift Ctrl Enter.

 

[Maune]

Thank you so much!
These worked perfectly!

 

[VBA Geek]

this will return 100 years of 5th tuesdays

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A1</th><td style="text-align:left">{=SMALL(<font color="Blue">IF(<font color="Red">(<font color="Green">DAY(<font color="Purple">ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"41845:78369"</font>)</font>)</font>)>28</font>)*(<font color="Green">WEEKDAY(<font color="Purple">ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"41845:78369"</font>)</font>),12</font>)=1</font>),ROW(<font color="Green">INDIRECT(<font color="Purple">"41845:78369"</font>)</font>)</font>),ROWS(<font color="Red">A$1:A1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[jasonb75]

this will return 100 years of 5th tuesdays

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A1</th><td style="text-align:left">{=SMALL(<font color="Blue">IF(<font color="Red">(<font color="Green">DAY(<font color="Purple">ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"41845:78369"</font>)</font>)</font>)>28</font>)*(<font color="Green">WEEKDAY(<font color="Purple">ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"41845:78369"</font>)</font>),12</font>)=1</font>),ROW(<font color="Green">INDIRECT(<font color="Purple">"41845:78369"</font>)</font>)</font>),ROWS(<font color="Red">A$1:A1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

I can only see 100 years of #NUM!'s coming from that formula weekday return type 12?

 

[VBA Geek]

are you sure,

i get

Excel 2013
	A
1	29/07/2014
2	30/09/2014
3	30/12/2014
4	31/03/2015
5	30/06/2015
6	29/09/2015
7	29/12/2015
8	29/03/2016
9	31/05/2016
Sheet1

etc etc

by increasing 78369 you can get even a longer period

 

[Rick Rothstein]

this will return 100 years of 5th tuesdays

Array Formulas

Cell	Formula
A1	{=SMALL(IF((DAY(ROW(INDIRECT("41845:78369")))>28)*(WEEKDAY(ROW(INDIRECT("41845:78369")),12)=1),ROW(INDIRECT("41845:78369"))),ROWS(A$1:A1))}

<tbody>
</tbody>

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

I can only see 100 years of #NUM!'s coming from that formula weekday return type 12?

are you sure,

Your formula will only work in XL2010 or above because the second argument that you used for the WEEKDAY function (12) was not introduced until then (older versions of Excel only had 1, 2 or 3 available for that second argument).

 

[VBA Geek]

thats right

you may user 1 as second argument for weekday function, and the make it equal to 3