Marking every third tuesday as a day off

Shinano

Board Regular
Joined
Dec 5, 2004
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
with a date in a1:

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

...will give the date of the 3rd thursday
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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