如何计算日期，但在微软访问中排除周末？ [英] How to calculate a date but exclude weekends in microsoft access?

问题描述

我正在为一个住房建设者工作，我正在努力帮助一个同事，得到一个公式，可以计算一个日期3天，但不包括周末。

  DateAdd（d，3，[TrussOrderDate]）
  

这是她正在使用的当前公式，她正在寻求计算日期，但不包括周末

解决方案

创建一个表，这将持有所有（银行/公共/特殊）假期。像（tbl_BankHolidays），

  holID | bankDate | bankHoliday | workDay 
 -------- + --------------- + ------------------- + ----------- 
 1 | 01/01/2014 |新年星期三
 2 | 18/04/2014 |耶稣受难节星期五
 3 | 21/04/2014 |复活节星期一|星期一
 4 | 05/05/2014 |五月初|星期一
 5 | 26/05/2014 |春假|星期一
 6 | 25/08/2014 |暑假|星期一
 7 | 25/12/2014 |圣诞节|星期四
 8 | 26/12/2014 |拳击节|星期五
 9 | 01/01/2015 |元旦|星期四
：
：
 16 | 28/12/2015 |拳击节|星期一
  

然后一个计算这些日期的函数。

函数addWorkDays（addNumber As Long，Date2 As Date）As Date
'******************** '
'代码由'
'提供Paul Eugin'
'********************'

Dim finalDate As Date
Dim i As Long，tmpDate As Date
tmpDate = Date2
i = 1
尽管我<= addNumber
如果工作日（tmpDate ）< 1和平日（tmpDate）<> 7和_
DCount（*，tbl_BankHolidays，bankDate =& CDbl（tmpDate））= 0然后i = i + 1
tmpDate = DateAdd（d tmpDate）
循环

addWorkDays = tmpDate

结束函数

当您需要将 n 号日添加到日期时，您只需使用与DateAdd函数相似的内容。您不指定String标识符，因此默认为只添加几天。那么第一个参数是要添加的天数。第二个是需要添加日期的日期。

代码可以一次循环一次，检查是否是周末（1 - vbSunday或7-vbSaturday），或者如果列在特别休息桌。如果是休息日或周末，请添加一天，而不增加计数器。当计数器等于您通过的数字时，循环将终止！

I work for a homebuilder and I am trying to help a co-worker out with getting a formula that can calculate a date 3 days out but exclude weekends.

DateAdd("d",3,[TrussOrderDate])   

this is the current formula she is using and she was looking to get this to calculate the date, but exclude weekends

解决方案

I would create a table, that will hold all the (Bank/Public/Special) holidays. Something like (tbl_BankHolidays),

holID   |   bankDate    |   bankHoliday     |   workDay
--------+---------------+-------------------+-----------
1       |   01/01/2014  |   New years day   |   Wednesday
2       |   18/04/2014  |   Good Friday     |   Friday
3       |   21/04/2014  |   Easter Monday   |   Monday
4       |   05/05/2014  |   Early May       |   Monday
5       |   26/05/2014  |   Spring holiday  |   Monday
6       |   25/08/2014  |   Summer holiday  |   Monday
7       |   25/12/2014  |   Christmas Day   |   Thursday
8       |   26/12/2014  |   Boxing Day      |   Friday
9       |   01/01/2015  |   New Year’s Day  |   Thursday
:
:
16      |   28/12/2015  |   Boxing Day      |   Monday

Then a Function to calculate these dates.

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 = " & CDbl(tmpDate)) = 0 Then i = i + 1
        tmpDate = DateAdd("d", 1, tmpDate)
    Loop

    addWorkDays = tmpDate

End Function

When you need to add n number of days to a date, you simply use, similar to DateAdd function. You do not specify the String identifier as this defaults to adding just days. Then first argument is the number of days to add. The second would be the date to which the days needs to be added.

The code works, by looping one day at a time, checks if it is a Weekend (1 - vbSunday, or 7 - vbSaturday), or if it listed in the special day off table. If it is a day off or weekend, add one day to the day without incrementing the counter. The loop will terminate when the counter is equal to the number you passed !

这篇关于如何计算日期，但在微软访问中排除周末？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！