I am sorry if this topic seems redundant, but I have searched and searched but cannot find the exact answer to my dilemma.

Does anyone have a code that will determine the elapsed time (in minutes or hours, either will work) between two dates excluding weekends and defined holidays? I have tried a ton of codes I have found online but nothing is working.

I have to determine the amount of time (in minutes or hours) between the time we receive product and the time it is available for sale but I need to exclude the weekends and holidays from the calculation. I have a table called T_TTR that houses the receipt date/time ([Receipt]) and the release date/time ([Release]). They are both formatted mm/dd/yyyy hh:mm.

I then have a table called Holidays with a date field called Holiday. I then list out all of our company holidays.

I then have a Module that runs the below code. I then call that Module in a query by using TTR: WeekDays([Receipt],[Release])

I am trying to get the number of minutes between the two dates/times while excluding weekends and holidays. The code is giving me the same as just the regular datediff function. It is not excluding the weekends or the holidays. I could work with it just excluding the weekends and manually calculate the time for the few holidays we have. But it is not doing anything like that.

I have searched and searched and I can find lots of code that will calculate the # of days between two dates, but not the number of minutes or hours and I can't get any of them to actually exclude the WE or Holidays.

Here is what I need to achieve:

12/30/2019 15:15 {exclude the holiday} - 01/02/2020 09:15 = 2521 (or 42.02 hours) instead I am getting 3960 minutes (or 66 hours). It is not excluding the holiday.

01/31/2020 10:30 {exclude the weekend} - 02/03/2020 15:45 = 1756 (or 29.3 horus) instead I am getting 4633 minutes (or 77.22 hours). It is not excluding the weekend.

02/06/2020 9:15 (nothing to exclude) - 02/06/2020 17:45 = 510 minutes (or 8.5 hours). The below codes results correctly on this one.

02/03/2020 8:00 (nothing to exlude) - 02/04/2020 11:15 = 1635 minutes (27.25 hours). The below coder results correctly on this one too.

When I do the simple DateDiff function in the query I get the exact same thing that the below code gives me. What am I doing wrong?

Any help is greatly appreciated.

Code:Public Function Weekdays(ByRef startDate As Date, _ ByRef endDate As Date _ ) As Integer ' Returns the number of weekdays in the period from startDate ' to endDate inclusive. Returns -1 if an error occurs. ' If your weekend days do not include Saturday and Sunday and ' do not total two per week in number, this function will ' require modification. On Error GoTo Weekdays_Error ' The number of weekend days per week. Const ncNumberOfWeekendDays As Integer = 2 ' The number of days inclusive. Dim varDays As Variant ' The number of weekend days. Dim varWeekendDays As Variant ' Temporary storage for datetime. Dim dtmX As Date ' If the end date is earlier, swap the dates. If endDate < startDate Then dtmX = startDate startDate = endDate endDate = dtmX End If ' Calculate the number of days inclusive (+ 1 is to add back startDate). varDays = DateDiff(Interval:="n", _ date1:=startDate, _ date2:=endDate) ' Calculate the number of weekend days. varWeekendDays = (DateDiff(Interval:="ww", _ date1:=startDate, _ date2:=endDate) _ * ncNumberOfWeekendDays) _ + IIf(DatePart(Interval:="w", _ Date:=startDate) = vbSunday, 1, 0) _ + IIf(DatePart(Interval:="w", _ Date:=endDate) = vbSaturday, 7, 0) ' Calculate the number of weekdays. Weekdays = (varDays - varWeekendDays) Weekdays_Exit: Exit Function Weekdays_Error: Weekdays = -1 MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Weekdays" Resume Weekdays_Exit End Function Public Function Workdays(ByRef startDate As Date, _ ByRef endDate As Date, _ Optional ByRef strHolidays As String = "Holidays" _ ) As Integer ' Returns the number of workdays between startDate ' and endDate inclusive. Workdays excludes weekends and ' holidays. Optionally, pass this function the name of a table ' or query as the third argument. If you don't the default ' is "Holidays". On Error GoTo Workdays_Error Dim nWeekdays As Integer Dim nHolidays As Integer Dim strWhere As String ' DateValue returns the date part only. startDate = DateValue(startDate) + TimeValue(startDate) endDate = DateValue(endDate) + TimeValue(endDate) nWeekdays = Weekdays(startDate, endDate) If nWeekdays = -1 Then Workdays = -1 GoTo Workdays_Exit End If strWhere = "[Holiday] >= #" & startDate _ & "# AND [Holiday] <= #" & endDate & "#" ' Count the number of holidays. nHolidays = DCount(Expr:="[Holiday]", _ Domain:=strHolidays, _ Criteria:=strWhere) Workdays = nWeekdays - nHolidays Workdays_Exit: Exit Function Workdays_Error: Workdays = -1 MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Workdays" Resume Workdays_Exit End Function