# Solved: Calculate xth day of month

Discussion in 'Business Applications' started by Pedro15, Apr 14, 2015.

Not open for further replies.

Joined:
Oct 5, 2008
Messages:
386

#### Attached Files:

File size:
26 KB
Views:
10
• ###### Calculate 4th tuesday each month.xls
File size:
14.5 KB
Views:
12

Joined:
Mar 27, 2008
Messages:
6,597
First Name:
Hans
I did not look at your files, just want to give you this.

I found this code a long time ago and have been using it since.

This formula will return the date of any day of the week you enter

Code:
```' We can generalize this to holidays that are defined as the Nth Day of some month,
' such as Martin Luther King's birthday, celebrated on the 3rd Monday of January.
' The following function will return the Nth DayOfWeek for a given month and year:
'SYntax NDOW:
'   y = Year
'   M = Month
'   N = Nth day of M month
'   DOW = Day of the week:  1 = Sunday, 2= Monday, etc.
Public Function NDow(Y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
NDow = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
End Function
```
The comments in the code are form the original code and should explain itsself

Joined:
Oct 5, 2008
Messages:
386
Hans,
Thanks for that though I am not that much savvy with macros so will try and struggle though it tomorrow.
In the meantime if someone can explain where I have gone wrong with my attempt it would be appreciated

Pedro

Joined:
Mar 27, 2008
Messages:
6,597
First Name:
Hans
I'll take a look and see if I can find it.
I will also implement the code I posted to see if it helps you

Joined:
Mar 27, 2008
Messages:
6,597
First Name:
Hans
I placed the code in the file and made some changes to the formulas and sheet
See attached file

#### Attached Files:

• ###### calculate-4th-tuesday-each-month-Hans.xls
File size:
48.5 KB
Views:
14

Joined:
Oct 5, 2008
Messages:
386
Thanks Hans thats brilliant.
Much appreciated.

Pedro

As Seen On