Calculating date with and without weekends. 1

[bri00032]

Hello all,
I am looking to calculate a date using three pieces of information. What is the best way to do this?

I would collect the following information.
Start Date
Number of days
Weekends (Yes/No)

From this I would like to take the start date and add the number of days and remove weekends only if no.

Any help on this would be great.

Thank you,

 

[Golom]

A bit of a kludge but try

Function EndDate(StartDate As Date, _
                 AddDays As Long, _
                 IncludeWeekends As Boolean) As Date

Dim n                           As Long
Dim NextDate                    As Date
Dim Step                        As Long
If IncludeWeekends Then
    EndDate = DateAdd("d", AddDays, StartDate)
Else
    n = 0
    Step = Sgn(AddDays)
    NextDate = StartDate
    Do Until n >= Abs(AddDays)
        NextDate = NextDate + Step
        If Weekday(NextDate) <> vbSaturday And _
           Weekday(NextDate) <> vbSunday _
           Then n = n + 1
    Loop
    EndDate = NextDate
End If
End Function

 

[bri00032]

Thank you Golom. I will try that out. Thank you again.

 

[bri00032]

Thank you again Golom.
That did not work for me. I ended up changing up this a bit and this is what I am doing.

I am working on building a scheduling database for work. I am collecting the following information.

Start date (date when job will start)
Hours on job (8 hours will be one day) (For ease could change change to days on job)
Work weekends (Yes or No check box)

From that information I would like to calculate the end date. With or without weekends (if checked or not)

Thank you for any help that you can give.

 

[Golom]

OK. What "did not work" exactly? Your revised process sounds to me like the original statement. Does the routine return incorrect values?

 

[MichaelRed]

There are several FAQs on this topic (in hte collection of MS Access fora) any several of them would provide complete code procedures for the process, except for the handling of the optional (weekend) days. Most routines also include the use of a holidays table, for the obvious reason.



MichaelRed