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/Forums.aspx/databases-ms-access/200809/1

[tina]

Re: Calculate a job End Date

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/Forums.aspx/databases-ms-access/200809/1

>

[Fred Zuckerman]

Re: Calculate a job End Date

"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]

Re: Calculate a job End Date

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]

Re: Calculate a job End Date

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]

Re: Calculate a job End Date

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/Forums.aspx/databases-ms-access/200809/1

[tina]

Re: Calculate a job End Date

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/Forums.aspx/databases-ms-access/200809/1

>

[Connie via AccessMonster.com]

Re: Calculate a job End Date

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]

Re: Calculate a job End Date

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]

Re: Calculate a job End Date

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

[tina]

Re: Calculate a job End Date

well, if you're adding whole days to the start date/time, as in 3 days is
exactly 72 hours, then just comment out the line of code that strips the
time out of the date value in the procedure, as

' dat = CDate(Fix(dat))

but if you need to add whole days and/or parts of days, that's another ball
of wax. you could run the current procedure, adjusted as above, to add/bump
days. but you'd need more code to handle adding hours and/or minutes,
because adding more time could bump the date to the next day, which could be
a weekend or holiday... anyway, you have a basic bump procedure already;
with some thought you can probably modify the code to handle incrementing
time. don't be afraid to play around with it, and remember the DateAdd()
function - it may prove useful in working with time components.

hth


"Connie via AccessMonster.c om" <u35472@uwewrot e in message
news:8a0fb7227b 677@uwe...

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
>

[Connie via AccessMonster.com]

Re: Calculate a job End Date

Hi Tina.

Thanks for your input. I'll work on that.

tina wrote:

>well, if you're adding whole days to the start date/time, as in 3 days is
>exactly 72 hours, then just comment out the line of code that strips the
>time out of the date value in the procedure, as
>
>' dat = CDate(Fix(dat))
>
>but if you need to add whole days and/or parts of days, that's another ball
>of wax. you could run the current procedure, adjusted as above, to add/bump
>days. but you'd need more code to handle adding hours and/or minutes,
>because adding more time could bump the date to the next day, which could be
>a weekend or holiday... anyway, you have a basic bump procedure already;
>with some thought you can probably modify the code to handle incrementing
>time. don't be afraid to play around with it, and remember the DateAdd()
>function - it may prove useful in working with time components.
>
>hth
>

>Hi Tina,
>>

>[quoted text clipped - 32 lines]

>Thanks,
>Connie

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200809/1