Detect Last working day of the month

[TonyforVBA]

Hey folks.

I've got quite a large distribution macro developed that I run on a weekly basis. I'm looking to included a piece of script that will include additional e-mail address if the macro is being run on the last working Thursday of the month.

I've checked the colander for the year and (unfortunately) every Thursday for the rest of the year is a working one. However, I would still like to incorporate the means to highlight any Office holiday's within a given year and have the macro determine, the last 'working' Thursday of the month based on this logic.

Would anybody have any suggestion of how to run such a script?

[Leith Ross]

Hello TonyforVBA,

This is really a 2 part question: 1) How to determine if today is the last Thursday and 2) Highlight holidays.

This macro is a UDF (User Defined Function) which will determine if the date given is the last Thursday of the month by returning True or False. This is used just like a worksheet formula.

Check if Today is Last Thursday of the Month

Please Login or Register  to view this content.

Example of using the UDF

Formula in Cell A1: =IsLastThursdayOfMonth(TODAY())

To answer the second part of your question, you should create a Named Range with the dates of the holidays and name it Holidays. You can then apply Conditional Formatting to highlight the dates that are holidays by using a Formula.

Conditional Formatting Example

In cell A1 is the date you want to check against the holidays. On the menu bar select Format > Conditional Formatting.... Select IsFormula and type in this formula:

=MATCH(Holidays,A1,0)