calculate a specific day of the week

[Dendalee]

I need to calculate from the entry date of a youth the specific date of the
Tuesday within a 30 day limit.

The youth in our program must see the doctor within 30 days, however, the
doctor is only in the office on Tuesdays. I want to calculate the nearest
Tuesday to the 30 day mark.

Thanks,

Dendalee

 

[Bill Edwards]

Public Function Tuesday30(datDate As Date) As Date
On Error GoTo Err_Label
Dim datThirtyDays As Date
datThirtyDays = DateAdd("dd", 30, datDate)
Do While Weekday(datThirtyDays, vbSunday) <> 3
datThirtyDays = DateAdd("d", -1, datThirtyDays)
Loop
Tuesday30 = datThirtyDays

Exit_Label:
Exit Function
Err_Label:
MsgBox Err.Description
Resume Exit_Label
End Function

 

[Valentín Playá]

Dendalee,

this function returns the nearest Tuesday in 30 days.

=iif(weekday([fecha1]+30;3)<5;
[fecha1]+30+(1-weekday([fecha1]+30;3));
[fecha1]+30+(8-weekday([fecha1]+30;3)))

were Fecha1 is the date to calculate from.

or you may put this function in a module

Function Tuesday30(DateFrom As Date) As Date
Tuesday30 = DateFrom + 30
If Weekday(Tuesday30, 3) < 5 Then
Tuesday30 = Tuesday30 + (1 - Weekday(Tuesday30, 3))
Else
Tuesday30 = Tuesday30 + (8 - Weekday(Tuesday30, 4))
End If
End Function

and call as Tuesday30(Fecha1) from a query or a form.

Regards,

Valentín Playá
Sonotronic S.A.
Madrid, Spain