# Marking every third tuesday as a day off

#### Shinano

##### Board Regular
In A1:A31 I have the date.

In B1:B31 I show weekdays by linking from A1:A1.

In C1:C31 I need to automatically display a day as a holiday in case it is e.g. the third tuesday in the month in question.

Any suggestion to how I get around this one?

Thanks a lot.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

##### MrExcel MVP
with a date in a1:

=A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+3)

...will give the date of the 3rd thursday

#### Shinano

##### Board Regular
Thanks, but I am not looking for the date. I need column C24 to automatically show e.g. "Day Off" in case the date in A24 is the third tuesday of the month.

Thanks

##### MrExcel MVP
=if(A1=(A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+5)),"Day Off","")

##### MrExcel MVP
via PM:

"I appreciate you coming up with the formular below. Can I ask you to explain it to me in detail though? I am not sure that I fully understand it. I want to apply it to other dates, but I am not sure what to change, and in particularly not why.

=if(A1=(A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+5)),"Day Off","")

OK:

=if(A1=(A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+5)),"Day Off","")

..structurally, this says "if a1 = the date of the 3rd thursday, 'Day-off', otherwise nothing"

A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+5)

we're looking for the third something (in this case Tuesday) - note that the maximum date for the third anything is the 21st.

a1-day(a1) - returns the beginning of the month
+22 - takes you one day beyond the max possible answer
-WEEKDAY(A1-DAY(A1)+5) the bit that works backwards to the target date. toggle the '5' to target alternative days (e.g. 3 for a Thursday)

#### Shinano

##### Board Regular
Thanks a lot.

One more question though.

How does 5 relate to Tuesday and 3 to Thursday?

Trying out I realized that it working backwards from Saturday to Sunday, 1-7. However, understanding it in detail is out of my league.

And, thanks a lot.

Replies
1
Views
158
Replies
0
Views
224
Replies
2
Views
394
Replies
2
Views
385
Replies
1
Views
374