finding dates from drop down

  • Thread starter Thread starter thomas donino
  • Start date Start date
T

thomas donino

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()
 
Thank you, I will try it

Joel said:
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()
Click to expand...
 
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.
 
Back
Top