Ytukyg

How can I calculate the number of working days between two dates without counting Saturdays, Sundays and holidays.

I have a table with the following fields:
enter image description here

I have the following table and what I want is that in the column called expr1 calculate the difference of working days.

Enter the code into Visual Basic, and call workingdays to the module

enter image description here

Replace the variables with the names of each column.

Function WorkingDays(ByVal FECHA_DE_VALIDACION_FA As Date, ByVal FECHA_IMPRESIÓN As Date) As Long

    While FECHA_DE_VALIDACION_FA <= FECHA_IMPRESIÓN

        Select Case True

            Case Weekday(FECHA_DE_VALIDACION_FA, vbMonday) > 5

            Case DCount("*", "Holidays", "Holiday = #" & Format(fromDate, "mm/dd/yyyy") & "#") > 0

            Case Else: WorkingDays = WorkingDays + 1

        End Select

        FECHA_DE_VALIDACION_FA = FECHA_DE_VALIDACION_FA + 1

    Wend

End Function

Then they tell me that in the query with the generator call the function Workingdays, but it does not give me a result.

enter image description here
enter image description here

I accept and run the query but I get an error.

As Tony suggests in the comments, you will first need to construct a table containing dates of holidays, as these can vary from year-to-year.

Then, rather than using functions such as DateDiff, the easiest method is probably to incrementally count the number of days between the two dates, without incrementing the counter should the date be a Saturday or Sunday, or a member of your holidays table, e.g.:

Function WorkingDays(ByVal fromDate As Date, ByVal toDate As Date) As Long

    While fromDate <= toDate

        Select Case True

            Case Weekday(fromDate, vbMonday) > 5

            Case DCount("*", "Holidays", "Holiday = #" & Format(fromDate, "mm/dd/yyyy") & "#") > 0

            Case Else: WorkingDays = WorkingDays + 1

        End Select

        fromDate = fromDate + 1

    Wend

End Function

The above assumes that you have a table called Holidays containing a field called Holiday with all of the holiday dates to be excluded.

The above function counts the number of working days inclusive of the two supplied dates.

For example, assuming that your Holidays table contains the following dates:

Then the function would return the following:

?WorkingDays(#2018-12-24#, #2019-01-04#)

 7 

To use the code, open the VBA IDE (Alt+F11), insert a new Module (Alt, I, M) and then paste the code into the module and save the module.

You can then call the function from anywhere where the Expression Builder is available.