Date Functions for Excel and VBA

DateFunction.xll

(Excel Versions)
NearestWeekEnding: 
=IF(WEEKDAY(TODAY())<=4,TODAY()-(WEEKDAY(TODAY()-1)),TODAY()+(8-WEEKDAY(TODAY())))

WeekEndingDate(inputcell,weindex):
inputcell = input date
weindex = day of week which is the end (1 = sunday, 7 = saturday)
=INT(IF(WEEKDAY(inputcell)=weindex,inputcell,IF(WEEKDAY(inputcell)>weindex,7,0)+inputcell+weindex-WEEKDAY(inputcell)))

DateFunctions.bas

'Nearest Week Ending Date
Public Function NearestWeekEnding() As Date
    NearestWeekEnding = _
        Fix( _
            IIf( _
                Weekday(Now()) <= 4, _
                Now() - (Weekday(Now() - 1)), _
                Now() + (8 - Weekday(Now())) _
                ) _
            )
End Function

'Returns the Week Ending Date of an input date, where WEIndex represents the day of week
'which is the end.
Public Function WeekEndingDate(ByVal InputDate As Date, Optional ByVal WEIndex As Integer = 1) As Date
    WeekEndingDate = _
        Fix( _
            IIf( _
                 Weekday(InputDate) = WEIndex,
                 InputDate,
                 IIf( _
                      Weekday(InputDate) > WEIndex,
                      7,
                      0
                 ) + InputDate + WEIndex - Weekday(InputDate)
            )
End Function