Workday function

[jonp]

I am currently working on a database that will calculate a date based upon date information entered on a form. This formula is rather simply because it adds 5 business days (excluding weekends and holidays) to the original date. Below is the formula that currently works in my Excel spreadsheet.

=Workday)F408, 5, {36161,36178,36206,36311,36409,36444,36475,36489,36542,36577,36675,36711,36773,36808,36853,36885})

From what I understand I will need to create a module to lookup this formula within Excel. What I do not understand is how to write this module and utilize it in my query? Does anyone have any suggestions??

Thanks,
JONP

 

[KDg]

Hi Jonp,

apologies for posting code this lame, but as you've got no other replies, I guess it may at least nudge someone else to posting(?) and in the meantime it does work.

Function WorkAday(ByVal dtmStartDate As Date, dtmEndDate As Date) As Byte
' Change bytes to Integer if more than 255 days is ever gonna happen
Dim bytTotalDays As Byte

Do Until dtmStartDate >= dtmEndDate

Select Case WeekDay(dtmStartDate)

Case Is = vbSunday

Case Is = vbSaturday

Case Else
bytTotalDays = bytTotalDays + 1
End Select
dtmStartDate = dtmStartDate + 1
Loop

WorkAday = bytTotalDays
End Function


MTH

Drew