PDA

View Full Version : find end of the...



av8tordude
01-14-2012, 03:40 PM
I would like to find the following...
end of any given month
end of any given year

Can some assist. Thank you for your help

p45cal
01-14-2012, 04:22 PM
Column D contains data from which dates are calculated:
End of a given month (2 ways):
=DATE(YEAR(D26),MONTH(D26)+1,0)
=EOMONTH(D26,0)

End of a given year given any date in that year:
=DATE(YEAR(D27),13,0)

End of year given the year only:
=DATE(D28,13,0)

av8tordude
01-14-2012, 04:31 PM
I should have mentioned in VBA...sorry

p45cal
01-14-2012, 04:35 PM
What data types are the source data?

Paul_Hossler
01-14-2012, 04:56 PM
You mean something like this?


Option Explicit

Sub test()
MsgBox LastDayOfMonth(#1/4/2012#)
MsgBox LastDayOfMonth(#2/14/2012#)
MsgBox LastDayOfMonth(#4/15/2012#)

MsgBox LastDayOfYear(#4/15/2012#)
MsgBox LastDayOfYear2(2016)

End Sub

Function LastDayOfMonth(d As Date) As Date
LastDayOfMonth = DateSerial(Year(d), Month(d) + 1, 1) - 1
End Function
Function LastDayOfYear(d As Date) As Date
LastDayOfYear = DateSerial(Year(d) + 1, 1, 1) - 1
End Function
Function LastDayOfYear2(d As Long) As Date
LastDayOfYear2 = DateSerial(d + 1, 1, 1) - 1
End Function


Paul