Date counting function in VBA

Question

I have Term, Creation Date, Days values available. Term can equal either "Beginning of next Month", "end of Month", "Now". I'm trying to create a function that takes "Term" and if the value in it is "Beginning of next month", it takes Creation date, goes to the end of that month, and then adds "days". So for example, If creation date is 1/1/2017 and the "Term" is "Beginning of next next month", and Days are 5. It goes to 1/31/2017 + 5, so the answer is 2/5/2017. I figured out an excel formula for it

IF(P3046="Beginning of next month",EOMONTH(DATE(YEAR(Creation date+days),MONTH(Creation date+days),15),0)+1,0)

But I cant figure out an equivalent in VBA to EOMONTH. This is what I tried:

IIF(Term = "Beginning of next month",DateSerial(year(Creation date+days),month(Creation date+days)+1,0)

But it didn't really work. It gives the answer but not the correct one. Does anyone see an error I don't see. I've been starting at it for a while so I might be missing something.

Thank you so much!!!

You should format your code more appropriately if you are writing VBA. There is no need for you to have everything on a single line. — JahKnows
Also, is "End of Month" and "Beginning of Next Month", just 1 day apart? Seems strange. — JahKnows

Kostas K. Kostas K. · Accepted Answer · 2017-07-20T14:48:34

You could define a function using a few nested DateAdd statements:

Public Function EOMONTH(ByVal d As Date, ByVal days As Integer) As Date
    EOMONTH = DateAdd("y", days, DateAdd("y", -1, DateAdd("m", 1, DateSerial(Year(d), Month(d), 1))))
End Function

?EOMONTH(#01/01/2017#,5)
'05/02/2017

Date counting function in VBA

1 Answers