get the latest day of the previous month

[Laurent M]

hello,

i have a date variable, and i would like with VBA to get
the latest day from the previous month

many thanks

 

[Harald Staff]

Function LastPrevDate(Dt As Date) As Date
LastPrevDate = DateSerial(Year(Dt), Month(Dt), 0)
End Function

Sub test()
MsgBox LastPrevDate(Now)
End Sub

HTH. Best wishes Harald

 

[Myrna Larson]

In case you specified VBA because you didn't think you could do it with
worksheet functions, the formula is =DATE(YEAR(TODAY()),MONTH(TODAY()),0)