Week Ending Date 2

[JesseL]

Hello all,

I am trying to come up with a solution to display the week-ending date on one of my reports, and the end of the week is on a Friday. Does anyone know a simple solution to this? Thanks! "Anyone who has never made a mistake has never tried anything new".
Albert Einstein (1879-1955)

 

[CosmoKramer]

I think this is what you're looking for. Enter this code, substituting your date field as the control source of an unbound text box:

=DateAdd("d",6-(Format([datefield],"w")),[datefield])

It formats the date field as a day number, subtracts that number from 6 (Friday), then adds that number to the date field's day number to get the Friday date of that week.

FYI - If a Saturday date is used, the date 1 day before will be returned.

 

[JesseL]

Thanks cosmokramer,

Thats exactly what i was looking for. Such a simple solution. You can add this star to your calendar "Anyone who has never made a mistake has never tried anything new".
Albert Einstein (1879-1955)

 

[MichaelRed]

A perhaps more generic version:

there actually SHOULD be some error checking here. The "WkDay" arg needs to be in the range of 1 to 7, otherwise the routine will fail. The values for "WkDay" are expected (by me) to be in the form shown in the examples: vbSunday ... vbSaturday --- But I really do do understand that someone(s) somewhere(s) dometime(s) will place an arbitrary value here and use the whine and complain option to berate me for the failue. Perhaps some ambitious coder will rectify this glaring omission.

Public Function basCurWkDayDt(DtIn As Date, WkDay As Integer) As Date

    'Michael Red    Tek-Tips thread703-360132   9/16/02

    Dim tmpDate As Date
    Dim Offset As Integer
    'Example Usage
    '? basCurWkDayDt(Date, vbMonday)
    '9/16/02

    '? basCurWkDayDt(#8/21/2002#, vbMonday)
    '8/19/02

    '? basCurWkDayDt(Date, vbFriday)
    '9/20/02


    'Guess the date (coluld be wrong week)
    tmpDate = DateAdd("d", 8 - Weekday(DtIn, WkDay), DtIn)
    Offset = DatePart("ww", DtIn) - DatePart("ww", tmpDate)

    basCurWkDayDt = DateAdd("ww", Offset, tmpDate)
    

End Function

With this, you can return the date of any day of the (current) week.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over