I have two drop down boxes from a list, year and month. These are used to
calculate expiration dates for various option contracts on stocks and
futures. I have had no problem writing the formulas for the easy ones (-:.
Here is where i am stumped

rules
expiration is the 3rd business day prior to the 25th of the month, if the 3
day prior to the 25th is NOT a business day, then it is the first business
day prior to that

for example if the 22nd falls on a Sunday, the third business day prior
would be Friday the 20th, if that Friday was a holiday, the formula would
have to pick up Thursday

The problem is how to telkl whan there is a holikday. You will need a list
of holidays. try the function below. You will have to change the holkidays
to match your calendar. Cal with the foolowing

=Target(A1) where A1 is a date (not string on the worksheet).

If yo are calling function from another VBA module you may have to sue
DateValue to convert a string date to a serial date like this

Sub Main()
MyDate = "1/10/09"
PriorDate = Prior3Days(DateValue(MyDate))
end Sub

Function Prior3Days(Target As Date)

' only process data theat is a date
If IsDate(Target) Then
Holidays = Array("1/1/09", "2/14/09", "7/4/09", _
"11/24/09", "12/25/09")

Prior3Days = Target
CountDays = 3
Do While CountDays 0
'subtract 1 day
Prior3Days = Prior3Days - 1
'check if the day is a weekend
If Weekday(Prior3Days) < 1 And _
Weekday(Prior3Days) < 7 Then

'check if the day is a holiday
Holiday = False
For Each itm In Holidays
If DateValue(itm) = Prior3Days Then
Holiday = True
Exit For
End If
Next itm
If Holiday = False Then
CountDays = CountDays - 1
End If
End If
Loop
End If
End Function


Function Prior3Days()

"thomas donino" wrote:

I have two drop down boxes from a list, year and month. These are used to
calculate expiration dates for various option contracts on stocks and
futures. I have had no problem writing the formulas for the easy ones (-:.
Here is where i am stumped

rules
expiration is the 3rd business day prior to the 25th of the month, if the 3
day prior to the 25th is NOT a business day, then it is the first business
day prior to that

for example if the 22nd falls on a Sunday, the third business day prior
would be Friday the 20th, if that Friday was a holiday, the formula would
have to pick up Thursday

Thank you, I will try it

"Joel" wrote:

The problem is how to telkl whan there is a holikday. You will need a list
of holidays. try the function below. You will have to change the holkidays
to match your calendar. Cal with the foolowing

=Target(A1) where A1 is a date (not string on the worksheet).

If yo are calling function from another VBA module you may have to sue
DateValue to convert a string date to a serial date like this

Sub Main()
MyDate = "1/10/09"
PriorDate = Prior3Days(DateValue(MyDate))
end Sub

Function Prior3Days(Target As Date)

' only process data theat is a date
If IsDate(Target) Then
Holidays = Array("1/1/09", "2/14/09", "7/4/09", _
"11/24/09", "12/25/09")

Prior3Days = Target
CountDays = 3
Do While CountDays 0
'subtract 1 day
Prior3Days = Prior3Days - 1
'check if the day is a weekend
If Weekday(Prior3Days) < 1 And _
Weekday(Prior3Days) < 7 Then

'check if the day is a holiday
Holiday = False
For Each itm In Holidays
If DateValue(itm) = Prior3Days Then
Holiday = True
Exit For
End If
Next itm
If Holiday = False Then
CountDays = CountDays - 1
End If
End If
Loop
End If
End Function


Function Prior3Days()

"thomas donino" wrote:

I have two drop down boxes from a list, year and month. These are used to
calculate expiration dates for various option contracts on stocks and
futures. I have had no problem writing the formulas for the easy ones (-:.
Here is where i am stumped

rules
expiration is the 3rd business day prior to the 25th of the month, if the 3
day prior to the 25th is NOT a business day, then it is the first business
day prior to that

for example if the 22nd falls on a Sunday, the third business day prior
would be Friday the 20th, if that Friday was a holiday, the formula would
have to pick up Thursday

You can always just do it on the worksheet (and dump values into the cell) by
using the Workday function:

=Workday(Start_Date,Days,Holidays)

Holidays can be a range. You may need to install the Analysis ToolPak Add-In.

So in your case, it would be
=Workday(Date(YearCell,MonthCell,DayCell),-3,Range(Holidays)).

That way you could have a set list of values for each month. I haven't been
able to do this in VBA but you could just source the Month and Year to select
the value.



"thomas donino" wrote:

Thank you, I will try it

"Joel" wrote:

The problem is how to telkl whan there is a holikday. You will need a list
of holidays. try the function below. You will have to change the holkidays
to match your calendar. Cal with the foolowing

=Target(A1) where A1 is a date (not string on the worksheet).

If yo are calling function from another VBA module you may have to sue
DateValue to convert a string date to a serial date like this

Sub Main()
MyDate = "1/10/09"
PriorDate = Prior3Days(DateValue(MyDate))
end Sub

Function Prior3Days(Target As Date)

' only process data theat is a date
If IsDate(Target) Then
Holidays = Array("1/1/09", "2/14/09", "7/4/09", _
"11/24/09", "12/25/09")

Prior3Days = Target
CountDays = 3
Do While CountDays 0
'subtract 1 day
Prior3Days = Prior3Days - 1
'check if the day is a weekend
If Weekday(Prior3Days) < 1 And _
Weekday(Prior3Days) < 7 Then

'check if the day is a holiday
Holiday = False
For Each itm In Holidays
If DateValue(itm) = Prior3Days Then
Holiday = True
Exit For
End If
Next itm
If Holiday = False Then
CountDays = CountDays - 1
End If
End If
Loop
End If
End Function


Function Prior3Days()

"thomas donino" wrote:

I have two drop down boxes from a list, year and month. These are used to
calculate expiration dates for various option contracts on stocks and
futures. I have had no problem writing the formulas for the easy ones (-:.
Here is where i am stumped

rules
expiration is the 3rd business day prior to the 25th of the month, if the 3
day prior to the 25th is NOT a business day, then it is the first business
day prior to that

for example if the 22nd falls on a Sunday, the third business day prior
would be Friday the 20th, if that Friday was a holiday, the formula would
have to pick up Thursday