Week Days Calculations

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I am considerably new to Access. I am working in Access
2000 and have a table called tblDates. In the table are
fields [DateOut] which will be manually entered. And field
[DateDue] which I will need to calculate and populate with
10 weekdays from [DateOut]. Any help auto populating the
field [DateDue] excluding weekends is greatly appreciated.

Thanks in advance.

Bill
 
Use the DateAdd Function. Very easy to use and nice help
within Access about it.

Drew
"We are the programmers that don't do anything...."
 
If you don't worry about holidays and your DateOut field is always Monday thru
Friday, then just add 14 days to the date.

DateAdd("d",14,DateOut)

If that won't work for you, then please post back.
 
Sorry, I may not have explained correctly. DateOut can be
any day of the week, weekend or holidays. DateDue will
always be 10 business days later.

Thanks,
Bill
-----Original Message-----
If you don't worry about holidays and your DateOut field is always Monday thru
Friday, then just add 14 days to the date.

DateAdd("d",14,DateOut)

If that won't work for you, then please post back.
I am considerably new to Access. I am working in Access
2000 and have a table called tblDates. In the table are
fields [DateOut] which will be manually entered. And field
[DateDue] which I will need to calculate and populate with
10 weekdays from [DateOut]. Any help auto populating the
field [DateDue] excluding weekends is greatly appreciated.

Thanks in advance.

Bill
Click to expand...
.
Click to expand...
 
If you are not concerned with Holidays, then you can use:
dateadd("d",(x\5)*7+(x mod 5)-2*((weekday(dd)+(x mod
5))>=7)-((weekday(dd)+(x mod 5))=1),dd)
where dd will be your date field, and x will be 10.
To exclude holidays, you will have to use a more complex VBA function.

Bill said:
Sorry, I may not have explained correctly. DateOut can be
any day of the week, weekend or holidays. DateDue will
always be 10 business days later.

Thanks,
Bill
-----Original Message-----
If you don't worry about holidays and your DateOut field is always Monday thru
Friday, then just add 14 days to the date.

DateAdd("d",14,DateOut)

If that won't work for you, then please post back.
I am considerably new to Access. I am working in Access
2000 and have a table called tblDates. In the table are
fields [DateOut] which will be manually entered. And field
[DateDue] which I will need to calculate and populate with
10 weekdays from [DateOut]. Any help auto populating the
field [DateDue] excluding weekends is greatly appreciated.

Thanks in advance.

Bill
Click to expand...
.
Click to expand...
Click to expand...
 
Here is a simple way to calc the date you are looking
for. As I said before, use the DateAdd Function. I have
two engineering degrees and the math formulas presented
in the other postings are quite extraordinary but far to
complex for this simple problem. Also, the previous
postings do not consider an ending date of Sat or Sunday
and calc wrong if the day starts on a Sat or Sun. THis
code will allow you to use any number of "weekday"
additions and will give you the correct business day for
the DateDue.

Here is the code:

Private Sub btn_AddDays_Click()
'This assumes that the 10 day count starts on the next
day.
'ie-Sunday start
'Day 1 Is monday
'2 is tues
'3 is wed
'4 is thurs
'5 is friday
'6 is monday
'and so on...

Dim NoD As Long 'Number of weekdays to add
Dim NoWD As Long 'Number of weekend days in number
Dim DateOut As Date 'This is the Input AcTextBox
Dim DateOutDay As Long 'This is the day of the week of
the DateOut
Dim DateDue As Date 'This Where the Date Due will be
displayed
Dim DateValue As Long 'Variable to use to test day of week

DateOut = Me.txtDateOut
NoD = Me.txtNumberofDaysToAdd
DateOutDay = Weekday(DateOut, vbSunday)

If DateOutDay = 1 Then
NoWD = (Round(NoD / 5, 0) * 2) - 2 'This accounts for
the Sunday Start
ElseIf DateOutDay = 7 Then
NoWD = (Round(NoD / 5, 0) * 2) - 1 'This accounts for
the Saturday Start
Else
NoWD = Round(NoD / 5, 0) * 2 'This is for all other
weekday Starts
End If

DateDue = DateAdd("d", NoD + NoWD, DateOut)
DateValue = Weekday(DateDue, vbSunday)

'Test to see if Date Due is falling on Weekday, if not
add the correct number of days
If DateValue = 7 Then
DateDue = DateAdd("d", 2, DateDue) 'IF the last day
falls on a Saturday then add two days to make the next
business day (Monday)
ElseIf DateValue = 1 Then
DateDue = DateAdd("d", 1, DateDue) 'IF the last day
falls on a Sunday then add one day to make the next
business day (Monday)
End If

Me.txtDateDue = DateDue

End Sub


Drew
"We are the programmers that dont do anything ...."
 
This code is wrong. It does not calculate the correct
DateDue for starts on Sat or Sun. IT returns the monday
after the Friday that it is truly due. Point- An item
that is rented on Sat or Sunday and due back in 5
business days is due back on Friday. You equation would
have the return date as the following Monday. A friend
of mine is in the rental equip business and he would not
be thrilled if his invoices posted your date... :o)

Nothing personal, nice equation though (from an guy with
2 engineering degrees and a masters in math)

Drew

-----Original Message-----
If you are not concerned with Holidays, then you can use:
dateadd("d",(x\5)*7+(x mod 5)-2*((weekday(dd)+(x mod
5))>=7)-((weekday(dd)+(x mod 5))=1),dd)
where dd will be your date field, and x will be 10.
To exclude holidays, you will have to use a more complex VBA function.

Bill said:
Sorry, I may not have explained correctly. DateOut can be
any day of the week, weekend or holidays. DateDue will
always be 10 business days later.

Thanks,
Bill
-----Original Message-----
If you don't worry about holidays and your DateOut
Click to expand...
field
is always Monday thru
Friday, then just add 14 days to the date.

DateAdd("d",14,DateOut)

If that won't work for you, then please post back.

Bill wrote:

I am considerably new to Access. I am working in Access
2000 and have a table called tblDates. In the table are
fields [DateOut] which will be manually entered. And field
[DateDue] which I will need to calculate and
Click to expand...
populate
with
10 weekdays from [DateOut]. Any help auto populating the
field [DateDue] excluding weekends is greatly appreciated.

Thanks in advance.

Bill
.
Click to expand...
Click to expand...


.
Click to expand...
 
Yeah, I had to pick which day the calculation would land on if it started on
Saturday or Sunday, and I picked Monday. If you want it to land on the
previous Friday, then use:
dateadd("d",(x\5)*7+(x mod 5)+((weekday(dd)+(x mod
5))>=7)+2*((weekday(dd)+(x mod 5))=1),dd)
If x is always =10, then use the following:
dateadd("d",14+((weekday(dd))>=7)+2*((weekday(dd))=1),dd)
or in Bill's case:
dateadd("d",14+((weekday([DateOut]))>=7)+2*((weekday([DateOut]))=1),[DateOut
])

Drew said:
This code is wrong. It does not calculate the correct
DateDue for starts on Sat or Sun. IT returns the monday
after the Friday that it is truly due. Point- An item
that is rented on Sat or Sunday and due back in 5
business days is due back on Friday. You equation would
have the return date as the following Monday. A friend
of mine is in the rental equip business and he would not
be thrilled if his invoices posted your date... :o)

Nothing personal, nice equation though (from an guy with
2 engineering degrees and a masters in math)

Drew

-----Original Message-----
If you are not concerned with Holidays, then you can use:
dateadd("d",(x\5)*7+(x mod 5)-2*((weekday(dd)+(x mod
5))>=7)-((weekday(dd)+(x mod 5))=1),dd)
where dd will be your date field, and x will be 10.
To exclude holidays, you will have to use a more complex VBA function.

Bill said:
Sorry, I may not have explained correctly. DateOut can be
any day of the week, weekend or holidays. DateDue will
always be 10 business days later.

Thanks,
Bill
-----Original Message-----
If you don't worry about holidays and your DateOut field
is always Monday thru
Friday, then just add 14 days to the date.

DateAdd("d",14,DateOut)

If that won't work for you, then please post back.

Bill wrote:

I am considerably new to Access. I am working in Access
2000 and have a table called tblDates. In the table are
fields [DateOut] which will be manually entered. And
field
[DateDue] which I will need to calculate and populate
with
10 weekdays from [DateOut]. Any help auto populating the
field [DateDue] excluding weekends is greatly
appreciated.

Thanks in advance.

Bill
.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top