WeekEnding Function 2

[Beeps]

Just wanted to share a tid bit

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

I had such a issue and with a time reporting database, and during the development of reports, it got a little difficult with the way that dates are sorted and grouped in charts and running sums. So I came up with this little function that will return the next following Sunday (Weekending) date of any date that is passed to it.


'********************************************************
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


Is used this function when a new record is added or modified, passing the date entered and recording the Weekending date in another field.

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,

Thanks!

 

[origapizni]

Here's another shorter way. Don't know that it's any better, just different.

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
    
    GetWeekEnding = DateAdd("d", (vdate > 1) * (vdate - 8), sDate)

End Function

 

[PaulBricker]

This should get you the WeekEnding Date

WeekEndingDate[DateFieldHere]-Weekday([DateFieldHere]))+7


Paul

 

[PaulBricker]

Sorry, my function returns the Saturday of the week the date is in (that's traditionally when our week ends). If you want Sunday you would add 8.

Paul

 

[SBendBuckeye]

Hey Beeps,

Thanks for taking the time to share your work. And thanks to the other guys for offering some refinements. That's what makes this such a great forum.

Have a great day all!

 

[origapizni]

And here's another twist (MUST set "Option Base 1" at top of Module):

Option Base 1

Function GetWeekEnding_a(dDate As Date) As Date
''' determines Sundays (Week Ending) date from _
    any date entered into the table _
    if entered date is already a Sunday, _
    keeps the entered date

        ''' var to hold result of weekday function
    Dim vdate As Integer
        ''' array to hold possible choices
    Dim DayArray() As Variant

        ''' Gets integer of weekday of argument
    vdate = Weekday(dDate)
        ''' days to add in DateAdd() function
    DayArray = Array(0, 6, 5, 4, 3, 2, 1)
    
    GetWeekEnding_a = DateAdd("d", DayArray(vdate), dDate)
    
End Function

 

[beetee]

How about:

WeekEnd = Fix((InputDate + 5) / 7) * 7 + 1

 

[Beeps]

Thanks for your posts!!

I'll try these out to see which version is faster and repost,
thanks again.

BP

 

[origapizni]

Can't get Fix((vDate + 5) / 7) * 7 + 1 to give the desired result.

but ((vdate + 5) \ 7) * (8 - vdate) works for me.

(note the divide is a "\" for the Integer divide operator, not the usual "/" standard divide operator. this is faster than a Fix() function call.)

Of course the (vdate + 5) \ 7) piece always returns 0 or 1 so it's logically the same as (vdate > 1), which is also simpler and faster. Which brings us around to
(vdate > 1) * (vdate - 8) again... >)

 

[beetee]

You typed:
Can't get Fix((vDate + 5) / 7) * 7 + 1 to give the desired result.


I typed:
WeekEnd = Fix((InputDate + 5) / 7) * 7 + 1


Explanation:
Dude... InputDate is the actual date, not a day number; so the statment:

"Of course the (vdate + 5) \ 7) piece always returns 0 or 1 so it's logically the same as (vdate > 1), which is also simpler and faster."

is inaccurate.

 

[origapizni]

OOPS! You're absolutely right - I thought you were replacing a value in the DateAdd() function. Instead, you were actually eliminating the need for the DateAdd() step entirely.

With the Integer Divide operator, your formula works with no function calls at all, just pure math

Function GetWeekEnding(dDate As Date) As Date
    WeekEnd = ((dDate + 5) \ 7) * 7 + 1
    GetWeekEnding = WeekEnd
End Function

No criticism intended. >)

 

[beetee]

Which is totally better and I really appreciate the input. I will try to use it in my code from now on. When I have more time to fool with it, it might be interesting to subject the code to some time trials.

 

[beetee]

I just noticed that \ is not a true integer divide; it has the same flaw that the cint.

To test this, let us try to strip the fractional time portion from a date.

Try the following (after 12 Noon) in the immediate window:

print now
print cdate(clng(now))
print cdate(now \ 1)
print cdate(fix(now))

only the last expression returns the 'correct' result.

 

[origapizni]

OOPS! Again. Good catch. Bummer.

I should have read Help:
"Before division is performed, the numeric expressions are rounded to Byte, Integer, or Long expressions"