Nearest Day

[cable]

Trying to write a function that returns the nearest day to a given date.

So give today's date and saturday it should return 24/01/04, if it was tuesday then 27/01/04.

I can do it if i just want nearest set day, cause its just a simple select case...but its far to early on a monday for me

 

[Mile-O]

Well, if you don't want to think then you can always search as there have been examples posted on this subject.

 

[cable]

bah...i looked as well...must be a really good monday

 

[Mile-O]

This should get you started then...

 

[cable]

Thank you very very much...i knew it should be a simple calculation but just couldn't get my head around it!

 

[Mile-O]

Of course that only goes forward. If you want to go back and forward I think you'll have to modify the code, run through it twice to get two dates and the one closest to the current date using the DateDiff() function is your nearest day.

 

[cable]

Right think i might have done it:

Public Function NearestDay(dDate As Date, iDay As Integer) As Date
'Returns the nearest day to give date
Dim I As Integer

For I = -3 To 3
    If WeekDay(dDate + I) = iDay Then NearestDay = dDate + I
Next I
End Function

Can anyone see any potential problems with it??

thought of one, is using dateadd really needed? or can i do it like i've done

 

[Mile-O]

I don't see any problems with it - except validating that the second parameter falls with 1 and 7.

I couldn't resist adding a little extra bit though:

Public Function NearestDay(dDate As Date, iDay As Integer, _
    Optional iWeeks As Integer) As Date

    Dim I As Integer
    
    For I = -3 To 3
        If Weekday(dDate + I) = iDay Then
            If IsMissing(iWeeks) Then
                NearestDay = dDate + I
            Else
                NearestDay = (dDate + I) + (iWeeks * 7)
            End If
        End If
    Next I
    
End Function

 

[cable]

ah yea validation, i did want to restict the day param to the vb constants or at least an enum, but couldn't see how in A97