Calc Weekdays

[scgoodman]

Any suggestions and best practices on the following.
I have commit times (which are days).

I need to calc the end date based on a start date and the number of days added. Excluding weekends and 10 us holidays.

Example.
Start date is 9/7 and needs to be delivered in 6 days. So I need to calculate the holiday and weekend. So the delivery would be 9/15.

Thanks

 

[RuralGuy]

Here's a function you can use:

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
     PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'     If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
     If Weekday(PlusWorkdays, vbMonday) <= 5 And _
      IsNull(DLookup("[HoliDate]", "tblHolidays", _
      "[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
      '-- The above Format of PlusWorkdays works with US or UK dates!
          intNumDays = intNumDays - 1
     End If
Loop

End Function

 

[scgoodman]

I copied this into module. I received a Compile Error.
Here is the code.

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
' If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[Date]", "tbl_Holidays", _
"[Date]" = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
'-- The above Format of PlusWorkdays works with US or UK dates!
intNumDays = intNumDays - 1
End If
Loop

End Function

 

[RuralGuy]

It is not a good idea to name a field Date since it is a reserved word. Change:
"[Date]" = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
to...
"[Date] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then

 

[scgoodman]

Thanks, I changed by table to match your code. tblHolidays and field HoliDate. Now I am getting a compile error. Thoughts.

Sorry new at this...

 

[scgoodman]

Disregard...it works!!!!
THANK YOU

 

[scgoodman]

Do you have any suggestions for excluding just sundays?

 

[RuralGuy]

Change the 5 to a 6.

 

[scgoodman]

This works great, but after I split...causes tons of latency. Any suggestions?

 

[RuralGuy]

Are you talking about splitting your db into FrontEnd and BackEnd? If so then I completely understand the latency. You will probably want to keep the Holiday table in the FrontEnd.

 

[scgoodman]

How do I do that? Sorry, still learning.

 

[RuralGuy]

Import the Holiday table into the FrontEnd and delete it from the Back end (in that order).

 

[scgoodman]

Did that, but now when I open the query...RunTime error '3078. Cannot find tblHolidays.

 

[RuralGuy]

Sorry, you needed to delete the Linked table first. It the same table exists when you impory Access appends a 1 to the name. Just delete the linked table and rename the table without the 1.

 

[scgoodman]

Quick Question:

This works great on Monday through Friday where if something is picked up on Monday and is to deliver on Friday and the transit time is 4 days. However if picked up on Sat/Sun, Monday should be day zero. Can you help me alter this code to reflect that?

THANKS!!

 

[RuralGuy]

Post the code you are currently using inside of the Code Tags. Press the "#" button before you paste.

 

[scgoodman]

Thanks...

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
     PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'     If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
     If Weekday(PlusWorkdays, vbMonday) <= 5 And _
      IsNull(DLookup("[HoliDate]", "tblHolidays", _
      "[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
      '-- The above Format of PlusWorkdays works with US or UK dates!
          intNumDays = intNumDays - 1
     End If
Loop
End Function

 

[scgoodman]

Notes: no weekends or holidays and the first day is always day zero...even if falls on saturday or sunday.

 

[RuralGuy]

So you want to could the weekend days if picked up on a weekend? Or just pretend the pickup was on the next Monday?

 

[scgoodman]

Exactly...so if picked up on sat/sun, monday would count as day zero.