Weekending Function 1

[Beeps]

Just wanted to share a tid bit

Ever have a an application which has records that include or use a date? Sometimes you might need to sort records such as this by the WeekEnding?


'********************************************************
Function GetWeekEnding(dDate As Date) As Date
'determines Sunday's(Week Ending)
'date from any date to enter into the table


Dim vdate As Integer 'var to hold result of weekday function
Dim sDate As Date 'Original date argument, modified to next following Sunday

sDate = dDate 'Set date to argument date
vdate = Weekday(dDate) 'Gets integer of weekday of argument



Select Case vdate 'From weekday value, determine what weekday it is and
'add appropriate days/dates to make equal with Sunday

Case Is = 1 'Is Sunday, nothing added
GetWeekEnding = DateAdd("d", 0, sDate)

Case Is = 2 'Monday, 6 days added
GetWeekEnding = DateAdd("d", 6, sDate)

Case Is = 3 'Tuesday, 5 days added
GetWeekEnding = DateAdd("d", 5, sDate)

Case Is = 4 'Wednesday, 4 days added
GetWeekEnding = DateAdd("d", 4, sDate)

Case Is = 5 'Thurday, 3 days added
GetWeekEnding = DateAdd("d", 3, sDate)

Case Is = 6 'Friday, 2 days added
GetWeekEnding = DateAdd("d", 2, sDate)

Case Is = 7 'Saturday, 1 day added
GetWeekEnding = DateAdd("d", 1, sDate)

End Select

End Function


This way, it's really easy to sort by week and by day for buisness oriented reporting and calculating.

Let me know if any questions or a better way of doing something like this,

(I put this in the Access modules forum as well, but could be used effectivly in Excel)
Thanks!

 

[DaleWatson123321]

Beeps,

Thanks for your contribution. I'm sure it can be made use of in VBA situations. A STAR for your efforts

For those interested, here's an alternative (formula) that can be used without having to use VBA...

=TODAY()+CHOOSE(WEEKDAY(TODAY(),2),6,5,4,3,2,1,0)

Additionally, if someone wants to format the cell to show the day of the week, plus the date, use the following "Custom" Format... use these steps...

1) In the Format - Cells window, click the "Number" tab, and then choose "Custom".

2) Under "Type", enter: dddd - mmm/dd/yy

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca