LASTDATE

There are 3 functions


LASTDATE_INTHISWEEK

Returns the date of the last day of a week.

Public Function LASTDATE_INTHISWEEK(Optional ByVal dtDateValue As Date) As Long 
Dim iDay As Integer
Dim iMonthNumber As Integer
Dim iYear As Integer

    Application.Volatile

    iDay = VBA.Day(dtDateValue - VBA.Weekday(dtDateValue, vbUseSystemDayOfWeek) + 7)
    iMonthNumber = VBA.Month(dtDateValue)
    iYear = VBA.Year(dtDateValue)

    LASTDATE_INTHISWEEK = VBA.DateSerial(iYear, iMonthNumber, iDay)

End Function

LASTDATE_INTHISMONTH

Returns the date of the last day of a month.

Public Function LASTDATE_INTHISMONTH( _ 
    Optional ByVal dtDateValue As Date, _
    Optional ByVal iMonthNumber As Integer, _
    Optional ByVal iYear As Integer) As Long

    If VBA.IsMissing(dtDateValue) Then
        iMonthNumber = iMonthNumber
    Else
        iMonthNumber = VBA.Month(dtDateValue)
        iYear = VBA.Year(dtDateValue)
    End If

    LASTDATE_INTHISMONTH = VBA.DateSerial(iYear, iMonthNumber + 1, 1) - 1

End Function

LASTDATE_INTHISYEAR

Returns the date of the last day of a year.

Public Function LASTDATE_INTHISYEAR(Optional ByVal dtDateValue As Date) As Integer 
    Application.Volatile
    
    If VBA.IsMissing(dtDateValue) Then
        dtDateValue = VBA.Date
    End If
    
    LASTDATE_INTHISYEAR = VBA.DateSerial(Year(dtDateValue), 12, 31)
End Function


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext