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

faeryluv

faeryluv

New Member
Joined
Sep 1, 2018
Messages
47
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! :)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Nice name! :)

How about:


ABCDEFGH
YearnDay (1=Sun, 2=Mon, etc.)Month

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/21/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2019[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2/18/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3/18/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4/15/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5/20/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6/17/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7/15/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8/19/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9/16/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10/21/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11/18/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12/16/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=DATE($D$2,G2,1+7*$E$2)-WEEKDAY(DATE($D$2,G2,8-$F$2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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! :hammer:
 
Last edited:
Upvote 0
Eric W said:
Nice name! :)

How about:


ABCDEFGH
YearnDay (1=Sun, 2=Mon, etc.)Month

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/21/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2019[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2/18/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3/18/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4/15/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5/20/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6/17/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7/15/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8/19/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9/16/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10/21/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11/18/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12/16/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A2[/TH]
[TD="align: left"]=DATE($D$2,G2,1+7*$E$2)-WEEKDAY(DATE($D$2,G2,8-$F$2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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! :hammer:
Click to expand...

Works PERFECTLY!! Thank you so much!!! Glad you like my name btw! :):)
 
Upvote 0
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,"")




faeryluv said:
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! :)
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Excel formula to return 3rd workday of a month
Replies
6
Views
225
rajeshm28
R
S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
61
AhoyNC
A
Jyggalag
  • Question Question
Date formula
2
Replies
11
Views
207
Alex Blakenburg
A
B
  • Question Question
calculate monthly value
Replies
0
Views
239
BrutalLogiC
B
T
  • Question Question
Populate Names against Dates and Course Names
Replies
2
Views
88
TerryE123
T

Forum statistics

Threads
1,223,956
Messages
6,175,614
Members
452,661
Latest member
Nonhle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top