伯爵工作日的X数量从日期输入 [英] Count X Amount of Working Days from Date Entered

问题描述

我有，用户需要输入开了日期Microsoft Access数据库：值。一旦进入，这会触发一个计算在另一个领域，截止日期（25 WD）：。这通过以下功能在后场的工作：

I have a Microsoft Access database where the user is required to enter a Date Opened: value. Once entered, this triggers a calculation in another field, Deadline (25 WD):. This works via the following function in the latter field:

=DateAdd("d",25,[Date opened])

我想要做什么，但是，是算25 工作天从输入的日期开业日期：。我有一个表假期，其中包含英国假期的名单，直到2020年。

What I want to do, however, is to count 25 working days from the date entered in Date Opened:. I have a table holidays which contains a list of UK holidays up until 2020.

我怎么可以合并为两个，所以到说话，以产生一个有效的截止日期（25 WD）：值，不计任何的日期在假期上市？

How can I merge to two, so-to-speak, in order to produce a valid Deadline (25 WD): value which does not count any of the dates listed in holidays?

例如，如果输入的日期为2015年1月1日，则该函数将计算从2015年1月1日25个工作日，这意味着它会排除下跌的时间和范围内的所有周末和任何节假日<在现场截止日期（25 WD）STRONG>生成的日期值也将是一个工作日（即不是周末或银行假日）

For example, if the date entered is 01/01/2015, then the function would count 25 working days from 01/01/2015, meaning that it would exclude all weekends and any bank holidays that fall within that period and the resulting date value in the field Deadline (25 WD) will also be a working day (i.e. not a weekend or bank holiday).

推荐答案

您可能需要一个UDF让你通过这个。喜欢的东西，

You might need a UDF to get you through this. Something like,

Function addWorkDays(addNumber As Long, Date2 As Date) As Date
'********************
'Code Courtesy of
'  Paul Eugin
'********************

    Dim finalDate As Date
    Dim i As Long, tmpDate As Date
    tmpDate = Date2
    i = 1
    Do While i <= addNumber
        If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And _
            DCount("*", "tbl_BankHolidays", "bankDate = " & Format(tmpDate, "\#mm\/dd\/yyyy\#")) = 0 Then i = i + 1
        tmpDate = DateAdd("d", 1, tmpDate)
    Loop

    Do While Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Or _
        DCount("*", "tbl_BankHolidays", "bankDate = " & Format(tmpDate, "\#mm\/dd\/yyyy\#")) <> 0
        tmpDate = DateAdd("d", 1, tmpDate)
    Loop

    addWorkDays = tmpDate
End Function

所以，当你加25天的日期，它会跳过存储在表中的所有周末和节假日 - tbl_BankHolidays 。

? addWorkDays(25, Date())
  25/06/2015 

希望这有助于！

Hope this helps !

编辑：我已经加入另一个循环，看是否结束日期适逢银行假日或周末，如果是这样，直到它到达一个工作日就会增加一天。

I have added another loop to see if the end date falls on a bank holiday or weekend, if it does it will add one more day until it reaches a weekday.

这篇关于伯爵工作日的X数量从日期输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！