date calculation

  • Thread starter Thread starter Rick B
  • Start date Start date
R

Rick B

How would one formulate an unbound text box to show the week ending date of
the current date assuming that the week ending date is always the Friday
following the current date?

Thank you in advance,

-rick
 
Try this

Sub Aweek(
MsgBox EndOfWeek(Date
End Su

Function EndOfWeek(tDate As Date) As Dat
Dim mDate As Dat
Dim i As Lon

D
mDate = DateAdd("d", i, tDate
i = i +
Loop Until Weekday(mDate) = vbFrida

EndOfWeek = mDat

End Function
 
Hi Rick

The following function will return the last day of the week containing a
given date:

Public Function EndOfWeek(ByVal GivenDate As Variant, _
Optional FirstDayOfWeek As Integer = vbUseSystemDayOfWeek, _
Optional IncludeTime As Boolean _
) As Variant
On Error Resume Next
If Not IsDate(GivenDate) Then GivenDate = CDate(GivenDate)
If Err = 0 Then
EndOfWeek = DateValue(GivenDate) - WeekDay(GivenDate, FirstDayOfWeek) + 7
If IncludeTime Then EndOfWeek = DateAdd("s", -1, EndOfWeek + 1)
End If
End Function

It takes three arguments:
GivenDate: any recognisable date format
FirstDayOfWeek: a number 1 (Sunday) through 7 (Saturday) which specifies
which day is to be considered the *first* day of the week. If not
specified, it assumes the system setting (defaults to Sunday)
IncludeTime: if false or not specified, returns just the date, or if
true returns the date *and* time one second before midnight on that date.

For your purpose you can paste the code above into a standard module, and
set the recordsource of your textbox to:
=EndOfWeek(Date(), 7)
 
How would one formulate an unbound text box to show the week ending date of
the current date assuming that the week ending date is always the Friday
following the current date?

Thank you in advance,

-rick
Click to expand...

Is a Friday date to be kept in the same week or changed to the
following week:
Kept in the same week.
In an unbound control:
=[DateField]+7-Weekday([DateField],7)

If you want a Friday date to be assigned to the Friday of the
following week, then:
=[DateField]+8-Weekday([DateField],6)
 
How would one formulate an unbound text box to show the week ending date of
the current date assuming that the week ending date is always the Friday
following the current date?
Click to expand...

I would guess that you mean for the box to show the current date *on*
a Friday... right? If so use the Weekday and DateAdd functions:

=DateAdd("d", 7 - Weekday(Date(), vbSaturday), Date())
 
Back
Top