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 11 2474
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,
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
"Connie via AccessMonster.com" <u35472@uwewrote in message
news:89eac425ac847@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.com 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","tblHolidays","Holiday=#" & (BegDate + t)
& "#")) Then
w = w + 1
Endif
If w=WorkDays Then
Exit Loop
Endif
Loop
FindEndDate = BegDate + t
End Function
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
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","tblHolidays","Holiday=#" & (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
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.com http://www.accessmonster.com/Uwe/For...ccess/200809/1
comments inline.
"Connie via AccessMonster.com" <u35472@uwewrote in message
news:89f118989afbc@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.com http://www.accessmonster.com/Uwe/For...ccess/200809/1
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
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.com" <u35472@uwewrote in message
news:89fb48fbfe751@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
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
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.com" <u35472@uwewrote in message
news:8a0fb7227b677@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
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.com http://www.accessmonster.com/Uwe/For...ccess/200809/1 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: SimonC |
last post by:
Help needed for a Javascript beginner.
As above in the subject... i need a javascript to run this, but not in
the form of a web-page. I want to calculate it between 2 fields in a
database that...
|
by: Jan Szymczuk |
last post by:
I'm creating an MS Access 2000 database where I have a number of people
entered using simple basic fields, Surname: SMITH
Forenames: John
DoB: 09/09/1958
Age:...
|
by: Rustan |
last post by:
Hi
Im using GregorianCalendar to find out the current years week numbers.
When the user chooses a week number in a dropdown i want to show that week
in a table with the corresponding dates.
For...
|
by: griemer |
last post by:
Hi every one,
Is there a way to calculate the timestamp of 00:00:00 last Sunday. For
me, this is the begin of the current week..
Or, how old is this week, in seconds
Any ideas?
Regards,
|
by: rohayre |
last post by:
Im a long time java developer and actually have never done anything
with java scripting. I'd like to write a short simple script for
calculating a date in the future based on today's date and a...
|
by: howzit |
last post by:
I have a set date in my MySQL database that records when a Sales Lead
is established and I echo that date as $row_rsLead;
I need to be able to calculate how many days that lead has existed....
|
by: Beemer Biker |
last post by:
I cant seem to get that date into any DateTime to make my calculation
directly by subtracting "01-01-0000" from "now".
After reading this:...
|
by: viporyo |
last post by:
I'm creating an Access Database 2003 for our Human Resource Dept. In the form, I will be adding a calendar wherein there will be date of hire, start date, etc. I need to be able to view the...
|
by: FishVal |
last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL.
So, let us say the problem is to calculate...
|
by: shimul |
last post by:
Hi All,
If I set up a form with start date (choose from calendar) and end date. Each week Start date (Monday ) and want to automatically calculate end date (sunday each week) . how can I...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |