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

 

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