Eliminate weekends/holidays from dates 1

[harrinj]

Hi everyone,

I have two date fields in a table ("Start" and "End&quot. In the report, I have a text control with =[End]-{Start] to give me the number of days between the two dates. I need to eliminate weekends (and holidays if possible)

(e.g. start = Thurs; End = next Tues. Ans = 3, not 5.)

I can't just subtract two from the answer because the dates might not cross over a weekend. Is there a fairly easy way to do this? Appreciate the help.... :-D

 

[flaniganmj]

Take everything, =[END]-[START], out of your target text box control, so it says unbound. Go to the Detail section event properties tab. Go to your event procedure for OnFormat and enter this.
___________________________________________________________
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim DayCount As Integer
Dim StartDate As Date

DayCount = 0
StartDate = START

While StartDate <= END

If Weekday(StartDate, vbMonday) < 6 Then
DayCount = DayCount + 1
End If

StartDate = StartDate + 1

Wend

TOTAL_DAYS.Value = DayCount

End Sub

____________________________________________________________


This will eliminate the weekends; it will get a little more complicated for the Holidays. Replace TOTAL_DAYS with the name of the target text box. You may be able to get the same result, but this loop works fine.

 

[MichaelRed]

see faq181-261
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over