计算作业结束日期 [英] Calculate a job End Date
问题描述
Hi Access Building Friends，
我正在为一个制造商建立一个数据库，他需要知道每项工作的预计结果。[br / >
我知道Start_Date和完成工作所需的总天数。
我需要在考虑周末和
假期。
我有一张桌子，列出了几年来每个假期的日期
来了。
我尝试使用我发现的名为Public Function WorkingDays的代码。
计算两个日期之间的工作日和假期，但是它没有推动End_Date提前完成所需的工作日。
我感谢您提供的任何帮助。
谢谢，
康妮
-
通过AccessMonster.com发布的消息
http://www.accessmonster.com/Uwe/For...ccess/200809/1
Hi Access Building Friends,
I am building a database for a manufacturer who needs to know the projected
End_Date of each job.
I know the Start_Date and the total days required to do the job.
I need to calculate the End_Date after taking into account Weekends and
Holidays.
I have a table which lists the dates of each holiday for several years to
come.
I tried using a code I found called, "Public Function WorkingDays" which
calculates the number of working days and holidays between two dates, but it
doesn''t push the End_Date ahead to fulfill the working days needed.
I appreciate any help you can offer.
Thanks,
Connie
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200809/1
推荐答案
好吧，试试这个函数，因为
公共函数calcDate（ByVal dat As Date，ByVal intAdd As Integer）As Date
Dim i As Integer，bump As Boolean，str As String
dat = CDate（Fix（dat））
i = 1
for i = 1 to intAdd
dat = dat + 1
Do
bump = False
如果DCount（1，" tbl00Holidays"，" hDate =＃"&数据& "＃"）0然后
dat = dat + 1
bump = True
结束如果
str =格式（dat，" ddd"）
如果str =" Sat"然后
dat = dat + 2
bump = True
ElseIf str =" sun"然后
dat = dat + 1
bump = True
结束如果
循环直到bump = False
下一页
calcDate = dat
结束函数
参数是：开始日期和天数。函数
返回计算的结束日期。该函数使用一个表来保存
假期和任何其他日期（周末之外）不应该计算的b $ b。该表是tbl00Holidays，日期字段是hDate - 但是
当然你可以用
假期表中的相应表格和字段名称替换。
hth
" Connie via AccessMonster.com" < u35472 @ uwewrote in message
news：89eac425ac847 @ uwe ...
okay, try this function, as
Public Function calcDate(ByVal dat As Date, ByVal intAdd As Integer) As Date
Dim i As Integer, bump As Boolean, str As String
dat = CDate(Fix(dat))
i = 1
For i = 1 To intAdd
dat = dat + 1
Do
bump = False
If DCount(1, "tbl00Holidays", "hDate = #" & dat & "#") 0 Then
dat = dat + 1
bump = True
End If
str = Format(dat, "ddd")
If str = "Sat" Then
dat = dat + 2
bump = True
ElseIf str = "sun" Then
dat = dat + 1
bump = True
End If
Loop Until bump = False
Next
calcDate = dat
End Function
the arguments are: a beginning date, and a number of days. the function
returns the calculated end date. the function uses a table that holds
holidays and any other dates (outside of weekends) that should not be
counted. the table is tbl00Holidays, and the date field is hDate - but of
course you can substitute the appropriate table and field names from your
holidays table.
hth
"Connie via AccessMonster.com" <u35472@uwewrote in message
news:89eac425ac847@uwe...
Hi Access Building Friends，
我正在为需要知道
的制造商构建数据库
Hi Access Building Friends,
I am building a database for a manufacturer who needs to know the
预计
projected
每项工作的结束日期。
我知道Start_Date和完成工作所需的总天数。
我需要计算考虑周末后的End_Date和
假期。
我有一张桌子，列出了几年来每个假期的日期
来了。
我尝试使用我发现的名为Public Function WorkingDays的代码。其中
计算两个日期之间的工作日和假期，但是
End_Date of each job.
I know the Start_Date and the total days required to do the job.
I need to calculate the End_Date after taking into account Weekends and
Holidays.
I have a table which lists the dates of each holiday for several years to
come.
I tried using a code I found called, "Public Function WorkingDays" which
calculates the number of working days and holidays between two dates, but
它是
it
并没有提前完成End_Date以满足所需的工作日。
我感谢您提供的任何帮助。
谢谢，
康妮
-
通过AccessMonster.com发布消息
http://www.accessmonster.com/Uwe/For ... ccess / 200809/1
" Connie via AccessMonster.com" < u35472 @ uwewrote in message
news：89eac425ac847 @ uwe ...
"Connie via AccessMonster.com" <u35472@uwewrote in message
news:89eac425ac847@uwe...
Hi Access Building Friends，
我正在为制造商建立一个数据库，他们需要知道
预计
每项工作的结束日期。
我知道Start_Date和完成工作所需的总天数。
考虑到周末后我需要计算End_Date和
假期。
我有一张桌子，列出了几年来每个假期的日期
来了。
我尝试使用我发现的名为Public Function WorkingDays的代码。其中
计算两个日期之间的工作日和假期数，但
并没有提前完成End_Date需要工作日。
我感谢您提供的任何帮助。
谢谢，
康妮
-
通过AccessMonster.com发布消息
http://www.accessmonster.com/Uwe/For...ccess/200809/1
您可以尝试类似下面的功能。这是未经测试的空气代码
但它可能有用;）我假设你有一个名为
tblHolidays的表，其中一个名为Holiday的日期字段包含你所有的
假期。该函数有两个参数（BegDate和WorkDays），并且
返回EndDate。
Fred Zuckerman
公共函数FindEndDate（ BegDate As Date，WorkDays As Integer）as Date
Dim t As Integer''总天数
Dim w As Integer''工作日
t = 0
w = 0
Do While True
t = t + 1
''查看是否有日期是周六，周日还是假日
如果工作日（BegDate + t）> = 2并且_
工作日（BegDate + t）< = 6和_
IsNull（Dlookup（Holiday，tblHolidays，Holiday =＃"&（BegDate + t）
&＃"） ）然后
w = w + 1
Endif
如果w = WorkDays那么
退出循环
Endif
循环
FindEndDate = BegDate + t
结束功能
You might try something like the function below. It''s untested "air" code
but it might work ;) I''ve made the assumption that you have a table named
tblHolidays with a date field named Holiday that contains all of your
holidays. The function takes two parameters (BegDate and WorkDays) and will
return the EndDate.
Fred Zuckerman
Public Function FindEndDate (BegDate As Date, WorkDays As Integer) As Date
Dim t As Integer ''Total Days
Dim w As Integer ''Work Days
t = 0
w = 0
Do While True
t = t + 1
'' Check To See If Date Is Sat, Sun, or Holiday
If Weekday(BegDate + t) >= 2 And _
Weekday(BegDate + t) <= 6 And _
IsNull(Dlookup("Holiday","tblHolidays","Holiday=#" & (BegDate + t)
& "#")) Then
w = w + 1
Endif
If w=WorkDays Then
Exit Loop
Endif
Loop
FindEndDate = BegDate + t
End Function
您好Tina，
感谢您的快速回复。
我很高兴尝试你的代码。
康妮:)
tina写道：
Hi Tina,
Thank you for your speedy reply.
I am excited to try your code.
Connie :)
tina wrote:
>好吧，试试这个函数，作为
公共函数calcDate（ByVal dat As Date，ByVal intAdd As Integer）As Date
Dim i As Integer，bump As Boolean，str As String
dat = CDate（Fix（dat））
i = 1
for i = 1 to intAdd
dat = dat + 1
Do
bump = False
如果DCount（1，tbl00Holidays，hDate =＃; &安培;数据& "＃"）0然后
dat = dat + 1
bump = True
结束如果
str =格式（dat，" ddd"）
如果str =" Sat"然后
dat = dat + 2
bump = True
ElseIf str =" sun"然后
dat = dat + 1
bump = True
结束如果
循环直到bump = False
下一页
calcDate = dat
结束函数
参数是：开始日期，和天数。函数
返回计算的结束日期。该函数使用一个表，该表保存
假期和不应计算的任何其他日期（周末之外）。表格是tbl00Holidays，日期字段是hDate - 但是当然你可以用
假期表中的相应表格和字段名称替换。
hth
>okay, try this function, as
Public Function calcDate(ByVal dat As Date, ByVal intAdd As Integer) As Date
Dim i As Integer, bump As Boolean, str As String
dat = CDate(Fix(dat))
i = 1
For i = 1 To intAdd
dat = dat + 1
Do
bump = False
If DCount(1, "tbl00Holidays", "hDate = #" & dat & "#") 0 Then
dat = dat + 1
bump = True
End If
str = Format(dat, "ddd")
If str = "Sat" Then
dat = dat + 2
bump = True
ElseIf str = "sun" Then
dat = dat + 1
bump = True
End If
Loop Until bump = False
Next
calcDate = dat
End Function
the arguments are: a beginning date, and a number of days. the function
returns the calculated end date. the function uses a table that holds
holidays and any other dates (outside of weekends) that should not be
counted. the table is tbl00Holidays, and the date field is hDate - but of
course you can substitute the appropriate table and field names from your
holidays table.
hth
> Hi Access Building Friends，
>Hi Access Building Friends,
[引用文字剪辑 - 17行]
[quoted text clipped - 17 lines]
>谢谢，
Connie
>Thanks,
Connie
-
通过 http://www.accessmonster.com
--
Message posted via http://www.accessmonster.com
这篇关于计算作业结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
