equation for business days and holidays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If I have a date (5/10/2005) what expression can I write that will allow me
to find 10 business day from this? I want the date to appear in the cell. But
I don't want holidays or weekends included?

Any thoughts? This one has me stumped!
Thanks in Advance!
 
If I have a date (5/10/2005) what expression can I write that will allow me
to find 10 business day from this? I want the date to appear in the cell. But
I don't want holidays or weekends included?

Any thoughts? This one has me stumped!
Thanks in Advance!
Click to expand...

Here's a function for you:

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
' If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'-- The above Format of PlusWorkdays works with US or UK dates!
intNumDays = intNumDays - 1
End If
Loop
End Function
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
Thanks...just a bit confused...is this an equation I would put into my query?
Or is this programming/"behind the scenes"....

Thanks again for your insight!

RuralGuy said:
If I have a date (5/10/2005) what expression can I write that will allow me
to find 10 business day from this? I want the date to appear in the cell. But
I don't want holidays or weekends included?

Any thoughts? This one has me stumped!
Thanks in Advance!
Click to expand...

Here's a function for you:

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
' If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'-- The above Format of PlusWorkdays works with US or UK dates!
intNumDays = intNumDays - 1
End If
Loop
End Function
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...
 
Thanks...just a bit confused...is this an equation I would put into my query?
Or is this programming/"behind the scenes"....

Thanks again for your insight!
Click to expand...
You would put this in a standard module named basFunctions and then you could
use the function in a query.

NewDate: = PlusWorkdays([OldDateField], 10)
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
Getting error messages, what are we doing wrong?

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'IfWeekday(PlusWorkdays, vbMonday)<=5 And_
IsNull(DLookup("[Holiday]","HolidayLookUp",_
"[DateofHoliday]="&Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'intNumDays=intNumDays - 1
End If
Loop
End Function

RuralGuy said:
Thanks...just a bit confused...is this an equation I would put into my query?
Or is this programming/"behind the scenes"....

Thanks again for your insight!
Click to expand...
You would put this in a standard module named basFunctions and then you could
use the function in a query.

NewDate: = PlusWorkdays([OldDateField], 10)
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...
 
You need a space before the _ when you're using it as a continuation
character. Post back (with the revised code) if that wasn't the only error.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MarieK said:
Getting error messages, what are we doing wrong?

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'IfWeekday(PlusWorkdays, vbMonday)<=5 And_
IsNull(DLookup("[Holiday]","HolidayLookUp",_
"[DateofHoliday]="&Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'intNumDays=intNumDays - 1
End If
Loop
End Function

RuralGuy said:
Thanks...just a bit confused...is this an equation I would put into my
query?
Or is this programming/"behind the scenes"....

Thanks again for your insight!
Click to expand...
You would put this in a standard module named basFunctions and then you
could
use the function in a query.

NewDate: = PlusWorkdays([OldDateField], 10)
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...
Click to expand...
 
That helped ONE error..still have another...it [red]highlights the line
"[DateofHoliday]="& Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then...it shades the "[DateofHoliday]="& ...

Thanks in advance!!!!


Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'IfWeekday(PlusWorkdays, vbMonday)<=5 And
IsNull(DLookup("[Holiday]","HolidayLookUp",_
"[DateofHoliday]="& Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'intNumDays=intNumDays - 1
End If
Loop
End Function

Douglas J. Steele said:
You need a space before the _ when you're using it as a continuation
character. Post back (with the revised code) if that wasn't the only error.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MarieK said:
Getting error messages, what are we doing wrong?

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'IfWeekday(PlusWorkdays, vbMonday)<=5 And_
IsNull(DLookup("[Holiday]","HolidayLookUp",_
"[DateofHoliday]="&Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'intNumDays=intNumDays - 1
End If
Loop
End Function

RuralGuy said:
On Thu, 10 Aug 2006 11:29:02 -0700, MarieK
<[email protected]>
wrote:

Thanks...just a bit confused...is this an equation I would put into my
query?
Or is this programming/"behind the scenes"....

Thanks again for your insight!

You would put this in a standard module named basFunctions and then you
could
use the function in a query.

NewDate: = PlusWorkdays([OldDateField], 10)
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...
Click to expand...
Click to expand...
 
You still have a continuation character without a preceeding space!

That helped ONE error..still have another...it [red]highlights the line
"[DateofHoliday]="& Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then...it shades the "[DateofHoliday]="& ...

Thanks in advance!!!!


Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'IfWeekday(PlusWorkdays, vbMonday)<=5 And
IsNull(DLookup("[Holiday]","HolidayLookUp",_
"[DateofHoliday]="& Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'intNumDays=intNumDays - 1
End If
Loop
End Function

Douglas J. Steele said:
You need a space before the _ when you're using it as a continuation
character. Post back (with the revised code) if that wasn't the only error.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MarieK said:
Getting error messages, what are we doing wrong?

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'IfWeekday(PlusWorkdays, vbMonday)<=5 And_
IsNull(DLookup("[Holiday]","HolidayLookUp",_
"[DateofHoliday]="&Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'intNumDays=intNumDays - 1
End If
Loop
End Function

:

On Thu, 10 Aug 2006 11:29:02 -0700, MarieK
<[email protected]>
wrote:

Thanks...just a bit confused...is this an equation I would put into my
query?
Or is this programming/"behind the scenes"....

Thanks again for your insight!

You would put this in a standard module named basFunctions and then you
could
use the function in a query.

NewDate: = PlusWorkdays([OldDateField], 10)
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...
Click to expand...
Click to expand...
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to calculate today's date plus 10 business dates? 1
How do I calculate future dates excluding weekends and holidays? 1
Target Due date and countdown for working days 1
Holiday Calculations 1
Infamous Business Day Count 7
Business Days Between Two Dates? 4
Help with Code and Working Days including Holidays 14
Subtract WorkDays 4

Back
Top