Date Function

[fabby1]

Hi

I have an input box for the date and the user then enters the number of days off

Is there a function I can use to add the number of days onto the selected date and exclude weekend and bank holidays

Thanks

Phil

 

[KenReay]

Hi

Try searching the forums for WorkingDays and/or MichaelRed, I am sure I remember a post by Michael which gave a function to do this

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[testkitt2]

Hi fabby1

I use this function to calculate number of working days. It does not account for Holidays.

Code was posted for me as you're asking for it now.


Function Work_Days(PickedUp As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer


PickedUp = DateValue(PickedUp)
Date() = DateValue(Date)
WholeWeeks = DateDiff("w", PickedUp, Date)
DateCnt = DateAdd("ww", WholeWeeks, PickedUp)
EndDays = 0
Do While DateCnt < Date
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

Testkitt2

 

[MichaelRed]

faq181-261



MichaelRed