Calculating Holidays (1 Viewer)

[scgoodman]

Team,
In need of some help. I have a script that I am using to calculate holidays, however having some issues with it. It is not using the first day after the holiday as day zero. For example, if a shipment is picked up on 5/29 and has a '5' day turnaround, it should be due on 6/8 (excluding holidays). Any help is appreciated.



Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
'-- Advance dteStart to Monday if needed.
If Weekday(dteStart, vbMonday) > 5 Then
PlusWorkdays = dteStart + (7 - Weekday(dteStart, vbMonday)) + 1
Else
PlusWorkdays = dteStart
End If
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

 

[tranchemontaigne]

It's been more than 10 years since I last needed to solve a similar problem and at that time I did it with VBA. Since holiday dates often change every year, you might need to use a lookup table with pre-populated values. This approach uses 3 steps

Step 1: (Here's the very inelegant part)
(1) use DateDiff to count the total number of elapsed days
(2) use DateDiff to count the number of Saturdays you need to deduct from the total elapsed days
DateDiff("w",fromDate,toDate,vbSaturday)
(3) use Date Diff to count the number of Sundays you need to deduct from the total elapsed days
DateDiff("w",fromDate,toDate,vbSunday)


Step 2:
(1) build a holidays table
(2) write a query to count the number of holidays that appear within your date range

Step 3:
Subtract the number of days calculated in step 2 from the number of days calculated in step 1
________
HONDA CT90

 

[vbaInet]

When you say holidays I'm assuming you mean like bank holidays and also for it to exlude weekends? Had a quick think and here's (possibly) another approach (based on my assumptions):

1. Looks like you have a holidays table already? If you don't create one (like tranchemontaigne suggested).

2. You would need three functions.

One for checking whether the PickupDate + 5 Days range falls within a weekend. This function will return the number of weekends whether 1 or 2 (if it falls within a weekend, or return 0 if it doesn't. Call this function WeekendDays

Second will be for checking whether the any of the dates fall within a holiday and you do this by querying the table using BETWEEN PickupDate AND (PickUpDate + 5 + WeekendDays). Use the BETWEEN in a DCount() function to get the number of occurrences.

The third will just be where both functions afore-mentioned will be called.