equation for business days and holidays

[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!

 

[RuralGuy]

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!

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.

 

[Guest]

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!

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!

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.

 

[RuralGuy]

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.

 

[Guest]

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

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.

 

[Douglas J. Steele]

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)

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

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.

 

[Guest]

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

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)

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.

 

[RuralGuy]

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

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)

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.

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.