Function to Calculate First Day of Week Returing Wrong Date

[jaaret]

I am attempting to create a function that will return the first workday of the week (Monday). The function I created is as follows:

Public Function Monday(DateDue As Date) As Date

Dim WDay As Integer

WDay = Weekday(DateDue)

Select Case WDay
Case WDay = 1
Monday = DateDue + 1
Case WDay = 2
Monday = DateDue
Case WDay = 3
Monday = DateDue - 1
Case WDay = 4
Monday = DateDue - 2
Etc...

The function is returning a date that is consistently 700 days prior to the DateDue. Example: if the DateDue is 07-Jan-2013 the function is returning 07-Feb-2011. Why am I getting this result?

 

[SkipVought]

hi,

Your Select Case LOGIC is off. You have an EXPRESSION for each case, rather than a VALUE...

    Select Case WDay
        Case 1
        Monday = DateDue + 1
        Case 2
        Monday = DateDue
        Case 3
        Monday = DateDue - 1
        Case 4
        Monday = DateDue - 2
'...

But why use a Rube Goldberg approch???

Public Function Monday(DateDue As Date) As Date

    Dim WDay As Integer
    
    WDay = Application.Weekday(DateDue)
    
    Monday = DateDue - (WDay - 2)
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

How are ya jaaret . . .

Perhaps:

[blue]Public Function Monday(DateDue) As Date
   Monday = DateDue - Weekday(DateDue) + 2
End Function[/blue]

[blue]Your Thoughts . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]