finding dates from drop down
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
|