Marking every third tuesday as a day off

Shinano

Board Regular
Joined
Dec 5, 2004
Messages
62
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.
 

Some videos you may like

Excel Facts

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

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
Joined
Dec 5, 2004
Messages
62
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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","")

I appreciate your help. "

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
Joined
Dec 5, 2004
Messages
62
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,708
Members
410,630
Latest member
Maggie28
Top