Calculate a job End Date

[Connie via AccessMonster.com]

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.c om
http://www.accessmonster.com/Uwe/For...ccess/200809/1

[tina]

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.c om" <u35472@uwewrot e in message
news:89eac425ac 847@uwe...

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.c om
http://www.accessmonster.com/Uwe/For...ccess/200809/1

[Fred Zuckerman]

"Connie via AccessMonster.c om" <u35472@uwewrot e in message
news:89eac425ac 847@uwe...

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.c om
http://www.accessmonster.com/Uwe/For...ccess/200809/1

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","tblH olidays","Holid ay=#" & (BegDate + t)
& "#")) Then
w = w + 1
Endif

If w=WorkDays Then
Exit Loop
Endif
Loop
FindEndDate = BegDate + t
End Function

[Connie via AccessMonster.com]

Hi Tina,

Thank you for your speedy reply.

I am excited to try your code.

Connie :)

tina wrote:

>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,

[quoted text clipped - 17 lines]

>Thanks,
Connie

--
Message posted via http://www.accessmonster.com

[Connie via AccessMonster.com]

Hi Fred,

Thank you for reading my post and giving my your "air" code.

I am hopeful.

Connie :)

Fred Zuckerman wrote:

>Hi Access Building Friends,

[quoted text clipped - 19 lines]

>Thanks,
Connie

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","tblH olidays","Holid ay=#" & (BegDate + t)
& "#")) Then
w = w + 1
Endif

If w=WorkDays Then
Exit Loop
Endif
Loop
FindEndDate = BegDate + t
End Function

--
Message posted via http://www.accessmonster.com

[Connie via AccessMonster.com]

Hi Tina,

This code works great! Thank you so much. :)
Could you tell me how to make it include the Start_Time and an End_Time?
I'm thinking it has to do with the "As Date" setting, but don't know how to
make it, "As Date/Time"

Thanks again,

Connie

tina wrote:

>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,

[quoted text clipped - 17 lines]

>Thanks,
Connie

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200809/1

[tina]

comments inline.

"Connie via AccessMonster.c om" <u35472@uwewrot e in message
news:89f118989a fbc@uwe...

Hi Tina,

This code works great! Thank you so much. :)

you're welcome, glad it works for you. :)

Could you tell me how to make it include the Start_Time and an End_Time?
I'm thinking it has to do with the "As Date" setting, but don't know how

to

make it, "As Date/Time"

well, in Access, a date value stored as Date/Time data type *always* has a
time - a default of midnight, if nothing else. so the return value of the
function does include a time, but it is the default. in fact, i specifically
included code in this function to strip the specific time out of the date
value before beginning the "bump" cycle:

dat = CDate(Fix(dat))

since you're beginning with a "Start" date and bumping that date by x number
of working days to get an "End" date, might i ask why you need to work with
time values?

>
Thanks again,

Connie

tina wrote:

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,

[quoted text clipped - 17 lines]

Thanks,
Connie

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200809/1

[Connie via AccessMonster.com]

Hi Tina,

Actually my Start_Date is a concatenation of the Start_Date and Start_Time.
That way it flows into a Start_Shift, and the time is in military time. So I
need an End_Time that would flow into an End_Shift. It is for a plastics
manufacturer.

Thanks so much! :)

Connie

tina wrote:

>comments inline.

>Hi Tina,

This code works great! Thank you so much. :)

you're welcome, glad it works for you. :)

>Could you tell me how to make it include the Start_Time and an End_Time?
I'm thinking it has to do with the "As Date" setting, but don't know how to
make it, "As Date/Time"

well, in Access, a date value stored as Date/Time data type *always* has a
time - a default of midnight, if nothing else. so the return value of the
function does include a time, but it is the default. in fact, i specifically
included code in this function to strip the specific time out of the date
value before beginning the "bump" cycle:

dat = CDate(Fix(dat))

since you're beginning with a "Start" date and bumping that date by x number
of working days to get an "End" date, might i ask why you need to work with
time values?

>Thanks again,

[quoted text clipped - 47 lines]

>Thanks,
Connie

--
Message posted via http://www.accessmonster.com

[tina]

sorry, hon, you've completely lost me. i can't picture how you would
*concatenate* two date/time values - are your Start_Date and Start_Time
stored as Date/Time data type, or as Text data type? also, i don't
understand what you mean by "flows into"...?
"Connie via AccessMonster.c om" <u35472@uwewrot e in message
news:89fb48fbfe 751@uwe...

Hi Tina,

Actually my Start_Date is a concatenation of the Start_Date and

Start_Time.

That way it flows into a Start_Shift, and the time is in military time.

So I

need an End_Time that would flow into an End_Shift. It is for a plastics
manufacturer.

Thanks so much! :)

Connie

tina wrote:

comments inline.

Hi Tina,

This code works great! Thank you so much. :)

you're welcome, glad it works for you. :)

Could you tell me how to make it include the Start_Time and an

End_Time?

I'm thinking it has to do with the "As Date" setting, but don't know

how to

make it, "As Date/Time"

well, in Access, a date value stored as Date/Time data type *always* has

a

time - a default of midnight, if nothing else. so the return value of the
function does include a time, but it is the default. in fact, i

specifically

included code in this function to strip the specific time out of the date
value before beginning the "bump" cycle:

dat = CDate(Fix(dat))

since you're beginning with a "Start" date and bumping that date by x

number

of working days to get an "End" date, might i ask why you need to work

with

time values?

Thanks again,

[quoted text clipped - 47 lines]

Thanks,
Connie

--
Message posted via http://www.accessmonster.com

[Connie via AccessMonster.com]

Hi Tina,

Oops! I guess I meant "add together" the date and time... So let's say the
Job Scheduler enters a Start Date of 9/12/08 and a Start Time of 22:30 (the
beginning of 3rd shift), I made a field called [Start_Date_Time] which just
adds the Start_Time to the Start_Date so I can use it in the calculation of
the End_Date_Time. Then I separate that into End_Date and End_Time so I can
use them in reports.

The calcDate retains the original time part of the Start_Time, and adds days
appropriately, but not the fraction of the last day.

I have been trying all day to get the code to show the time part. It seems
to be rounding down the intAdd to the nearest whole number.

By the way, I am working on a button to "Check Capacities" which will prevent
double booking of parts being scheduled to be built on machines.... which is
the main reason that I need the time part to show up is so they can know what
shift it will end on.

I really really appreciate your help.

Thank you.

Connie :)
tina wrote:

>sorry, hon, you've completely lost me. i can't picture how you would
*concatenate * two date/time values - are your Start_Date and Start_Time
stored as Date/Time data type, or as Text data type? also, i don't
understand what you mean by "flows into"...?

>Hi Tina,

[quoted text clipped - 36 lines]

>Thanks,
Connie

--
Message posted via http://www.accessmonster.com