Workdays Module (1 Viewer)

[JDeezy]

Hey all,
I found this workdays module on a site and I am very new to all this VBA stuff but I just need to edit a few things on it to get it to work correctly and any pointers would be greatly appreciated.

It is counting the dates between two different days. I need it to produce a specific date. I'm mostly using this module to produce the last business day so i can use it in all my queries and macros. Any help on this would officially save my life. Thanks in advance.

'modDirectDateFunctions-------
Option Compare Database
Option Explicit

Public Function GetChristmas(dtInYear As Date) As Date
GetChristmas = DateSerial(Year(dtInYear), 12, 25)
End Function


Public Function GetEaster(dtInYear As Date) As Date
Dim d As Integer
Dim y As Integer
Dim DT As Date

y = Year(dtInYear)
d = (19 * (y Mod 19) + 24) Mod 30
DT = DateAdd("d", d, DateSerial(y, 3, 22))
DT = DateAdd("d", (8 - Weekday(DT)) Mod 7, DT)
GetEaster = DateSerial(Year(dtInYear), Month(DT), Day(DT))
End Function

Public Function GetIndependence(dtInYear As Date) As Date
GetIndependence = DateSerial(Year(dtInYear), 7, 4)
End Function

Public Function GetLabor(dtInYear As Date) As Date
'First Monday in September
GetLabor = DateSerial(Year(dtInYear), 9, NthXDay(1, vbMonday, DateSerial(Year(dtInYear), 9, 1)))
End Function


Public Function GetMemorial(dtInYear As Date) As Date
'Last Monday in May
GetMemorial = DateSerial(Year(dtInYear), 5, Day(LastXDay(DateSerial(Year(dtInYear), 5, 1), vbMonday)))
End Function

Public Function GetNewYears(dtInYear As Date) As Date
GetNewYears = DateSerial(Year(dtInYear), 1, 1)
End Function


Public Function GetThanksgiving(dtInYear As Date) As Date
'Fourth Thursday in November
GetThanksgiving = DateSerial(Year(dtInYear), 11, NthXDay(4, vbThursday, DateSerial(Year(dtInYear), 11, 1)))
End Function


Public Function LastXDay(dtD As Date, DayConst As Integer) As Date
LastXDay = DateSerial(Year(dtD), Month(dtD) + 1, (-Weekday(DateSerial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod 7)
End Function

Public Function NthXDay(N As Integer, d As Integer, dtD As Date) As Integer
NthXDay = (7 - Weekday(DateSerial(Year(dtD), Month(dtD), 1)) + d) Mod 7 + 1 + (N - 1) * 7
End Function

Public Function CountHolidays(dtStart As Date, dtEnd As Date)
Dim lngTemp As Long

lngTemp = 0
lngTemp = lngTemp + Abs(GetNewYearsObserved(dtStart) >= dtStart) + Abs(GetNewYearsObserved(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetEasterMonday(dtStart) >= dtStart) + Abs(GetEasterMonday(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetMemorial(dtStart) >= dtStart) + Abs(GetMemorial(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetIndependenceObserved(dtStart) >= dtStart) + Abs(GetIndependenceObserved(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetLabor(dtStart) >= dtStart) + Abs(GetLabor(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetVeteransObserved(dtStart) >= dtStart) + Abs(GetVeteransObserved(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetThanksgiving(dtStart) >= dtStart) + Abs(GetThanksgiving(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetChristmasObserved(dtStart) >= dtStart) + Abs(GetChristmasObserved(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
If lngTemp < 0 Then lngTemp = 0
CountHolidays = lngTemp
End Function

Public Function CountWeekdays(dtStart As Date, dtEnd As Date) As Integer
CountWeekdays = DateDiff("d", dtStart, dtEnd) + 1 - CountWeekendDays(dtStart, dtEnd)
End Function

Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As Integer
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = (LEDay(dtEnd, 7) - GEDay(dtStart, 7)) / 7 + 1
intSat = DateDiff("d", GEDay(dtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = (LEDay(dtEnd, 1) - GEDay(dtStart, 1)) / 7 + 1
intSun = DateDiff("d", GEDay(dtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDays = (intSat + intSun + Abs(intSat) + Abs(intSun)) / 2
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function

Public Function CountWorkdays(dtStart As Date, dtEnd As Date) As Integer
'Note: using observed holidays precludes holidays falling on a weekend.
CountWorkdays = CountWeekdays(dtStart, dtEnd) - CountHolidays(dtStart, dtEnd)
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + Weekday(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - Weekday(dtX)) Mod 7, dtX)
End Function

Public Function GetEasterMonday(dtInYear As Date) As Date
GetEasterMonday = DateAdd("d", 1, GetEaster(dtInYear))
End Function

Public Function GetIndependenceObserved(dtInYear As Date) As Date
Dim dtTemp As Date

dtTemp = DateSerial(Year(dtInYear), 7, 4)
If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", -1, dtTemp)
If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetIndependenceObserved = dtTemp
End Function

Public Function GetChristmasObserved(dtInYear As Date) As Date
Dim dtTemp As Date

dtTemp = DateSerial(Year(dtInYear), 12, 25)
If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", -1, dtTemp)
If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetChristmasObserved = dtTemp
End Function

Public Function GetNewYearsObserved(dtInYear As Date) As Date
Dim dtTemp As Date

dtTemp = DateSerial(Year(dtInYear), 1, 1)
If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", 2, dtTemp)
If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetNewYearsObserved = dtTemp
End Function

Public Function GetVeteransObserved(dtInYear As Date) As Date
Dim dtTemp As Date

dtTemp = DateSerial(Year(dtInYear), 11, 11)
If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", -1, dtTemp)
If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetVeteransObserved = dtTemp
End Function

Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'end--modDirectDateFunctions-------

 

[jzwp22]

If the module does not do what you want, then it would probably better to find one that is more similar to what you are after especially if your coding abilities are still being developed. It sounds like you are trying to find the last business day; the last business day before what? Can you provide some more details on how you plan to use this function in a query, macro or other module?

It might be good to define what you mean by a business day or better yet what days are not business days. In most cases, non-business days would include holidays, Saturday and Sunday. Using the weekday() function you can determine which dates are Saturday or Sunday easy enough. For the holidays, I have seen others load all of the holidays and any other special days off in a table. They would then reference that table in the code.

 

[JDeezy]

I'm sorry. I thought it would be pretty apparent in the coding. It states all the holidays in the coding already and business days would include M-F excluding holidays. I have looked at a lot of different modules but this is the only one I found that I dont have to make a table every year to make it work.

In a nut shell, all I want it to do is pull the previous working day. And I'm sure i will have a couple of other uses for it down the road but for now this will do. It will be used in macros and queries both. So instead of using Date()-1 i want to use Workdays()-1 and it would calculate with observation to national holidays and weekends.

 

[jzwp22]

Yes, I did notice that it was finding the holidays, but I did not know if those and weekends were your only days off and I did not check to see if the functions return the correct date for the holidays.

Assuming that the various functions return the correct date, then the job is to create another function that utilizes these functions to evaluate the previous date compared to any day you provide to the function.

Just thinking about it, I would probably set up a loop of some sort. I would first evaluate whether the previous day is a Saturday or Sunday and then increment the date to the one previous and so forth. Then I would check against the holidays perhaps loading them into an array first using the functions you found and doing the comparisons against the array elements.

I'll see if I can come up with something.

 

[JDeezy]

Thank you so much. Yeah thats what I was thinking but I know a little Java and C++ programming and I know almost nothing about VBA code. Luckily now in college they dont dwell on access too much and I got a job were all they use is access and Cobalt haha.

 

[jzwp22]

I created a database with the code and a form (frmTest) to use to test the function; it is attached. Please let me know if it meets your needs.

 

[JDeezy]

WOW. That is amazing!!! It really is perfect!!! You just saved me like an hour a day entering stupid dates. Thank you so much!!

 

[jzwp22]

You're welcome; glad I was able to help out! Those functions you found will come in handy too.

 

[datAdrenaline]

As an alternative, I would suggest you look at the code found here:

fNetWorkdays() and fAddWorkdays()

To get the previous workday of a given date you would simply call fAddWorkdays(somepasseddate, 0)

All the holidays are table driven, thus allowing for complete flexibility.