Matthew Dyer pretended :
I was hoping to be able to take the following worksheet formula and convert
it into a formula to be used with VBA coding. What the formula does is return
the 15th of the month if today() is less than the 15th or the 15th of the
next month if today() is greater than the 15th. Furthermore, if the 15th
happens to be a saturday or sunday, it will return the preceeding friday
(13th or 14th as necessary). I accept there may be a much easier way to do
this so I am open to suggestions on how to simplify this as well. Thanks in
advance for any help!!


=IF(WEEKDAY((IF(DAY(TODAY())<15,(15-DAY(TODAY()))+TODAY(),EOMONTH(TODAY(),0)+15)),2)=6 ,(IF(DAY(TODAY())<15,(15-DAY(TODAY()))+TODAY(),EOMONTH(TODAY(),0)+15))-1,IF(WEEKDAY((IF(DAY(TODAY())<15,(15-DAY(TODAY()))+TODAY(),EOMONTH(TODAY(),0)+15)),2)=7 ,(IF(DAY(TODAY())<15,(15-DAY(TODAY()))+TODAY(),EOMONTH(TODAY(),0)+15))-2,(IF(DAY(TODAY())<15,(15-DAY(TODAY()))+TODAY(),EOMONTH(TODAY(),0)+15))))

This is a Sub:
=====================================
Public Sub Test1()
Dim MyDate As Date, n As Integer

MyDate = [Trial!E5]

If DatePart("d", MyDate) <= 15 Then
Select Case Weekday(CDate(DatePart("m", MyDate) & "/15"),
vbSaturday)
Case 1
n = -1
Case 2
n = -2
End Select
MsgBox CDate(DatePart("m", MyDate) & "/" & 15 + n)
Else
Select Case Weekday(CDate(DatePart("m", MyDate) + 1 & "/15"),
vbSaturday)
Case 1
n = -1
Case 2
n = -2
End Select
MsgBox CDate(DatePart("m", MyDate) + 1 & "/" & 15 + n)
End If

End Sub
==============================================


This is a Function:
==============================================
Public Function Test2(MyDate As Date) As Date
Dim n As Integer

If DatePart("d", MyDate) <= 15 Then
Select Case Weekday(CDate(DatePart("m", MyDate) & "/15"),
vbSaturday)
Case 1
n = -1
Case 2
n = -2
End Select
Test2 = CDate(DatePart("m", MyDate) & "/" & 15 + n)
Else
Select Case Weekday(CDate(DatePart("m", MyDate) + 1 & "/15"),
vbSaturday)
Case 1
n = -1
Case 2
n = -2
End Select
Test2 = CDate(DatePart("m", MyDate) + 1 & "/" & 15 + n)
End If

End Function
=============================================

Tell me if they work.

Bruno