VBA to get TARGET rate fixing dates

[John99]

Hi all!

I am trying to build a function that takes a value date for an instrument as an argument and returns a fixing date. The fixing lag is -2 TARGET business days and TARGET is closed as follows:

"The TARGET system is closed on Saturdays and Sundays and on the following public holidays in all participating countries: New Year (January 1), Good Friday, Easter Monday, Labour Day (May 1), and the first and second days of Christmas (December 25 and December 26). "

So if a value date is Monday or Tuesday the lag is -4 and the fixing dates are previous thursday and friday accordingly. In addition to that I have to include the other days when TARGET is closed: So for example if December 25th is Friday and my value date is monday then my fixing date should be wednesday.

I have done some code but seems that i am not getting so far at the moment

Function TargetDate(TheoFixingDate As Date) As Date

Dim Fixinglag, yr As Integer, Christmas1 As Date, Christmas2 As Date, NewYears As Date, LabourDay As Date, GoodFriday As Range, EasternMonday As Date

Fixinglag = -2

yr = Year(TheoFixingDate)
Christmas1 = DateSerial(yr, 12, 25)
Christmas2 = DateSerial(yr, 12, 26)
NewYears = DateSerial(yr, 1, 1)
LabourDay = DateSerial(yr, 5, 1)



If Weekday(TheoFixingDate) = vbMonday Or Weekday(TheoFixingDate) = vbTuesday Then
    TargetDate = TheoFixingDate + Fixinglag - 2
Else
    TargetDate = TheoFixingDate + Fixinglag
End If
    



End Function

And I am getting the Good Friday days from spreadsheet (because i had no idea how to build a VBA to calculate the days):

Good Friday
21.3.2008
10.4.2009
2.4.2010
22.4.2011
6.4.2012
29.3.2013
18.4.2014
3.4.2015
25.3.2016
14.4.2017
30.3.2018
19.4.2019
10.4.2020
2.4.2021
15.4.2022
7.4.2023
29.3.2024
18.4.2025

And then Eastern Monday days are Good Friday days + 3.

Any ideas how I could build my function? Especially the Eastern Days seem to be giving me a headache.

Any help appreciated..

Br,
John

[jrussell]

Try the NETWORKDAYS function:
http://office.microsoft.com/en-us/ex...091901033.aspx

[John99]

jrussell,

Thanks for your reply. I considered NETWORKDAYS function earlier but I wasn't able to solve how I would get it work. So if I am using the value date as an End date argument and then I would have my holidays as an optional argument. If I want the answer to be two business days before End date.. I am not sure how to build this.

How could I get the answer to be always two business days before End date..?

[DonkeyOte]

I suspect you actually want to use WORKDAY
(like NETWORKDAYS this requires activation of Analysis ToolPak pre XL2007)

So you would say have

=WORKDAY(A1,-2,holidaysrange)

The above would return the date that is 2 working days prior to Date as held in A1 where working days are determined first by being Mon-Fri and 2nd by NOT being present within holidays range.

It might be an idea to give some sample dates with expected outcomes etc...

[John99]

WORKDAY function actually seems to be doing the work. Thanks!