Formula to show dates for the 3rd Tues of every month

[faeryluv]

Hello, all! I am looking for a formula to display the date of the 3rd Tuesday of every month in 2019. Example: Jan 15, Feb 19, Mar 19, Apr 16, May 21, Jun 18...

I will need to modify the formula to show other dates such as the first Monday of every month, the last Friday of every month, etc.

I am setting up support groups based on their schedule and I need to know the dates. I want to save time so I don't have to look at a calendar each time.

Thank you in advance!

 

[Eric W]

Nice name!

How about:

	A	B	C	D	E	F	G	H
1				Year	n	Day (1=Sun, 2=Mon, etc.)	Month
2	1/21/2019			2019	3	2	1
3	2/18/2019						2
4	3/18/2019						3
5	4/15/2019						4
6	5/20/2019						5
7	6/17/2019						6
8	7/15/2019						7
9	8/19/2019						8
10	9/16/2019						9
11	10/21/2019						10
12	11/18/2019						11
13	12/16/2019						12

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula A2 =DATE($D$2,G2,1+7*$E$2)-WEEKDAY(DATE($D$2,G2,8-$F$2)) <tbody> </tbody>

<tbody>
</tbody>

Put the formula in A2, and your parameters in D2:G2. If you put the months down column G, you can drag the formula down column A to get the dates for the rest of the year. This handles the "nth" "weekday" of the month. It won't help if you want the "last" weekday of the month. That formula would be:

=DATE(Year,Month+1,0)-WEEKDAY(DATE(Year,Month+1,0),DOW+10)

where DOW is 1=Sunday, 2=Monday, etc.

Hope this helps!

 

[faeryluv]

Nice name!

How about:

	A	B	C	D	E	F	G	H
1				Year	n	Day (1=Sun, 2=Mon, etc.)	Month
2	1/21/2019			2019	3	2	1
3	2/18/2019						2
4	3/18/2019						3
5	4/15/2019						4
6	5/20/2019						5
7	6/17/2019						6
8	7/15/2019						7
9	8/19/2019						8
10	9/16/2019						9
11	10/21/2019						10
12	11/18/2019						11
13	12/16/2019						12

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula A2 =DATE($D$2,G2,1+7*$E$2)-WEEKDAY(DATE($D$2,G2,8-$F$2)) <tbody> </tbody>

<tbody>
</tbody>

Put the formula in A2, and your parameters in D2:G2. If you put the months down column G, you can drag the formula down column A to get the dates for the rest of the year. This handles the "nth" "weekday" of the month. It won't help if you want the "last" weekday of the month. That formula would be:

=DATE(Year,Month+1,0)-WEEKDAY(DATE(Year,Month+1,0),DOW+10)

where DOW is 1=Sunday, 2=Monday, etc.

Hope this helps!

Works PERFECTLY!! Thank you so much!!! Glad you like my name btw!

 

[caabyyc]

Put following formula in any cell then press "Control+shift+enter" (3 keys), then copy down

=IFERROR(SMALL(IF((WEEKDAY(ROW($43466:$43830),2)=2)*(DAY(ROW($43466:$43830))<8),ROW($43466:$43830),"/"),ROW(A1))+14,"")

Hello, all! I am looking for a formula to display the date of the 3rd Tuesday of every month in 2019. Example: Jan 15, Feb 19, Mar 19, Apr 16, May 21, Jun 18...

I will need to modify the formula to show other dates such as the first Monday of every month, the last Friday of every month, etc.

I am setting up support groups based on their schedule and I need to know the dates. I want to save time so I don't have to look at a calendar each time.

Thank you in advance!

 

[Eric W]

Happy to help!