By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,445 Members | 3,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,445 IT Pros & Developers. It's quick & easy.

Autofill Date by 7 business days

P: 7
i need help again on calculating business days excluding holidays
i have a field called assign date and i need to calculate 7 business days excluding holidays ( already have a table for holiday) from that date. i have read some forums on getting total business day but not the other way around
Aug 12 '08 #1
Share this Question
Share on Google+
1 Reply


RuralGuy
Expert 100+
P: 375
Here's a function you can use:
Expand|Select|Wrap|Line Numbers
  1. Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
  2.  
  3. PlusWorkdays = dteStart
  4. Do While intNumDays > 0
  5.      PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
  6. '     If Weekday(PlusWorkdays, vbMonday) <= 5 Then
  7. '-- If you have a Holiday Table use the next IF instead!
  8.      If Weekday(PlusWorkdays, vbMonday) <= 5 And _
  9.       IsNull(DLookup("[HoliDate]", "tblHolidays", _
  10.       "[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
  11.       '-- The above Format of PlusWorkdays works with US or UK dates!
  12.           intNumDays = intNumDays - 1
  13.      End If
  14. Loop
  15.  
  16. End Function
Aug 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.