convert spreadsheet formula into VBA formula

[Matthew Dyer]

I was hoping to be able to take the following worksheet formula and convertit 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 15thhappens 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))))

 

[Ron Rosenfeld]

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))))

Well, although you can surely do this in VBA, there is a much simpler worksheet formula than what you are using. Assuming the date of interest is in A1 (of course, you could substitute TODAY() for A1 in the formula below):

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>15),16),-1)

There is uncertainty in your description in that you do not indicate what you want to happen if TODAY() is equal to the 15th of the month. I chose to leave it at the 15th of "this" month. But if you want it to be the 15th of the next month (adjusted for weekends), then merely change the equality from ">" to ">=" :

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>=15),16),-1)

 

[Matthew Dyer]

That is a much easier formula than my mess of nested nonsense... Thanks! I developed the following formula to determine the same basic critera but forEOM as opposed to the 15th of the month. Could you look it over for me? Thank you again in advance! You are a TON of help!!!

=IF(WEEKDAY(EOMONTH(TODAY(),0),2)=6,EOMONTH(TODAY(),0)-1,IF(WEEKDAY(EOMONTH(TODAY(),0),2)=7,EOMONTH(TODAY(),0)-2,EOMONTH(TODAY(),0)))

 

[Ron Rosenfeld]

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>=15),16),-1)

That is a much easier formula than my mess of nested nonsense... Thanks! I developed the following formula to determine the same basic critera but for EOM as opposed to the 15th of the month. Could you look it over for me? Thank you again in advance! You are a TON of help!!!

=IF(WEEKDAY(EOMONTH(TODAY(),0),2)=6,EOMONTH(TODAY(),0)-1,IF(WEEKDAY(EOMONTH(TODAY(),0),2)=7,EOMONTH(TODAY(),0)-2,EOMONTH(TODAY(),0)))

There's no reason why you can't simplify it using the same principal:

=WORKDAY(EOMONTH(A1,0)+1,-1)

returns the same as your formula. Replace A1 with TODAY()

 

[Bruno Campanini]

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