Set Future Date in TextBox

[dtay1132]

I have a form that has two text boxes for dates (these drive my report criteria). I need to be able to calculate a future date that is on a working day (M-F) off of the [EndingDate} text box. It would be even better if I could somehow weed out holidays, but I don't want to get greedy. Anyone able to help me tackle this?

 

[TheAceMan1]

How are ya dtay1132 . . .

We need some [blue]criteria for determining the future date![/blue]

2wks, 3wks, some months/years, a formula, after ending date . . . what?

or . . . according to your needs, how will you determine what target date to shoot for, and what if its a weekend or holiday . . . how do you intend to compensate?

See Ya! . . . . . .

 

[dtay1132]

Excellent point! I need to have a future date 3 business days from the [EndingDate, not including the date entered by the user in the text box.

Ending Date of Tuesday 1/3/2006 returns [FutureDate] of Friday 1/6/2006.
Ending date of Friday 1/6/2006, returns [FutureDate] of 1/11/2006. (This example skips Sat and Sun in the business day count). Hope that makes sense.

Thanks for the help.

 

[edsuk]

dtay1132

Below is a simple proc that prints out the date 3 working days from today:

Sub DeriveNewDate()

Dim curDate As Date
Dim i As Byte

curDate = Date
i = 0

Do
                
    curDate = DateAdd("d", 1, curDate)

    If Format(curDate, "w", vbMonday) <> 6 And Format(curDate, "w", vbMonday) <> 7 Then
        i = i + 1
    End If
   
Loop Until i = 3
    
Debug.Print Format(curDate, "mm/dd/yyyy")

End Sub

HTH

Mark...

 

[JerryKlmns]

Function ForwardDate(EndingDate As Date) As Date
Select Case Weekday(EndingDate)
Case 1, 2, 3
ForwardDate = EndingDate + 3
Case 7
ForwardDate = EndingDate + 4
Case 4, 5, 6
ForwardDate = EndingDate + 5
End Select
End Function

 

[MichaelRed]

see faq705-3213 of faq181-264

in particular, if you need to STRICTLY count business days, you also need to account for the holidays which are observed.



MichaelRed

 

[TheAceMan1]

dtay1132 . . .

Sorry about the delay (thread got burried in my e-mail).

Assuming you have a table of holidays as follows:

[tt][blue]tblHolidays
***********
HolidayID as PrimaryKey (autonumber)
HD as Date[/blue][/tt]

In the [blue]AfterUpdate[/blue] event of [blue]EndingDate[/blue], copy paste the following:

[blue]   Dim NewDate As Date, WD As Integer
   Dim Criteria As String, idx As Integer
   
   NewDate = Me!EndingDate
   
   Do
      NewDate = NewDate + 1
      WD = Weekday(NewDate)
      Criteria = "[HD] = #" & NewDate & "#"
      
      If IsNull(DLookup("[HD]", "tblHolidays", Criteria)) And _
         WD <> vbSaturday And _
         WD <> vbSunday Then
         idx = idx + 1
      End If
   Loop Until idx = 3
   
   Me![purple][b]TextboxName[/b][/purple] = NewDate[/blue]

See Ya! . . . . . .