On Sun, 1 Mar 2009 21:50:19 -0800 (PST), CurlyDave
wrote:


try this
=DATE(YEAR(E1),MONTH(E1),DAY(E1)+CHOOSE(WEEKDAY(E 1),
1,0,-1,-2,-3,3,2,1))

Can you give me a parameter for E1 ?
What are all those numbers ?

Check out Dave McRitchie's site

Yes I had a look... much to take in... as time permits.

I did get something working... but it's pretty rough.

--
Function CfrmDate(ByVal ChosenDate)
Dim a, Diff, x
Diff = DateDiff("d", #12/30/1939#, ChosenDate)
x = Diff / 7
If x < Int(Diff / 7) Then
a = x - Int(x)
x = Int(x)
Select Case a
Case Is = 0.5
' add a day
Do
ChosenDate = DateAdd("d", 1, ChosenDate)
Diff = DateDiff("d", #12/30/1939#, ChosenDate)
x = Diff / 7
Loop Until x = Int(Diff / 7)
Case Else
'subtract a day
Do
ChosenDate = DateAdd("d", -1, ChosenDate)
Diff = DateDiff("d", #12/30/1939#, ChosenDate)
x = Diff / 7
Loop Until x = Int(Diff / 7)
End Select
End If
CfrmDate = ChosenDate
End Function
--


Be good to get it all in one line!

Cheers - Kirk