DATELAST

There are 3 functions
For instructions on how to add a function to a workbook refer to the page under Inserting Functions

DATELAST_INTHISWEEK

Returns the date of the last day of a week.

Public Function DATELAST_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) 

    DATELAST_INTHISWEEK = VBA.DateSerial(iYear, iMonthNumber, iDay) 
End Function 

DATELAST_INTHISMONTH

Returns the date of the last day of a month.

Public Function DATELAST_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 

    DATELAST_INTHISMONTH = VBA.DateSerial(iYear, iMonthNumber + 1, 1) - 1 
End Function 

DATELAST_INTHISYEAR

Returns the date of the last day of a year.

Public Function DATELAST_INTHISYEAR( _ 
Optional ByVal dtDateValue As Date) As Integer 

    Application.Volatile 
    
    If VBA.IsMissing(dtDateValue) Then 
        dtDateValue = VBA.Date 
    End If 
    
    DATELAST_INTHISYEAR = VBA.DateSerial(Year(dtDateValue), 12, 31) 
End Function