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