计算不带假期的工作日数；周末 [英] Calculate Number of Working Days without Holidays & Weekends

问题描述

如何计算两个日期之间的工作天数，而不计算周六，周日和节假日.

我有一个包含以下字段的表格: 在此处输入图片描述

我有下表，我想要的是在称为expr1的列中计算工作日的差额.

在Visual Basic中输入代码，并在模块中调用工作日

在此处输入图片描述

用每列的名称替换变量.

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

然后他们告诉我，在使用生成器的查询中，调用了Workdays函数，但没有得到结果.

在此处输入图片描述 在此处输入图片描述

我接受并运行查询，但出现错误.

解决方案

正如Tony在评论中所建议的那样，您首先需要构造一个表，其中包含节假日的日期，因为这些日期可能会每年不同. >

然后，最简单的方法可能是递增计数两个日期之间的天数，而不是使用诸如DateDiff之类的函数，如果日期是星期六或星期日，或者您的某个成员，则不增加计数器假期表，例如:

 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
 

以上假设您有一个名为Holidays的表，其中包含一个名为Holiday的字段，其中要排除所有假日日期.

上面的函数计算包括两个提供的日期在内的工作日数.

例如，假设您的Holidays表包含以下日期:

然后该函数将返回以下内容:

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

要使用该代码，请打开VBA IDE( Alt + F11 )，插入新的模块( Alt ， I ， M )，然后将代码粘贴到模块中并保存该模块.

然后，您可以在表达式生成器可用的任何地方调用该函数.

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.

这篇关于计算不带假期的工作日数；周末的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！