Business Days

M

Mary

I'm able to count the number of business days between two dates, but now I
need to calculate two business days out from a date field. Can you tell me
how to do this?

The count is done in a query using this criteria that I got from another post:
Bus_Days: dhCountWorkdaysA([ACTIVITY_DATE],Date())

Is there a similar function that will display the actual date that is two
business days out?

Thanks!
Mary
 
K

KARL DEWEY

This will display the next business day.

IIf(Weekday([ACTIVITY_DATE])=6,[ACTIVITY_DATE]+4,IIf(Weekday([ACTIVITY_DATE])=5,[ACTIVITY_DATE]+4,IIf(Weekday([ACTIVITY_DATE])=4,[ACTIVITY_DATE]+2,IIf(Weekday([ACTIVITY_DATE])=3,[ACTIVITY_DATE]+2,[ACTIVITY_DATE]+2))))
 
M

Michael Gramelspacher

[email protected]>, [email protected]
says...
I'm able to count the number of business days between two dates, but now I
need to calculate two business days out from a date field. Can you tell me
how to do this?

The count is done in a query using this criteria that I got from another post:
Bus_Days: dhCountWorkdaysA([ACTIVITY_DATE],Date())

Is there a similar function that will display the actual date that is two
business days out?

Thanks!
Mary
Click to expand...
This is somewhat similiar, but is for school days.
SchoolCalendar has all calendar days. Holidays has all non-
school days other than Saturdays and Sundays, i.e., holidays,
in-service training, snow days, spring break, etc.

Today is 16 Feb 2007. Monday is a US Holiday. Next school day
here is 20 Feb 2007. Second school day from now is 21 Feb 2007.

CREATE TABLE SchoolCalendar
(class_date DATETIME NOT NULL PRIMARY KEY)

CREATE TABLE Holidays
(holiday DATETIME NOT NULL PRIMARY KEY,
holiday_name VARCHAR (30) NOT NULL)

PARAMETERS [Number of school days in future:] Short;
SELECT MIN(SchoolCalendar.class_date) + [Number of school days
in future:]
- 1 AS future_school_date
FROM SchoolCalendar
WHERE (((SchoolCalendar.class_date) > DATE())
AND ((WEEKDAY([SchoolCalendar].[class_date])) <> 7
AND (WEEKDAY([SchoolCalendar].[class_date])) <> 1)
AND ((EXISTS (SELECT Holidays.holiday
FROM Holidays
WHERE Holidays.holiday =
SchoolCalendar.class_date)) = False));
 
K

Ken Sheridan

Mary:

Try this:

Public Function BusinessDaysOut(dtmDate As Date, NumberOfDays As Integer) As
Date

Dim n As Integer
Dim dtmNextDay As Date

dtmNextDay = dtmDate

For n = 1 To NumberOfDays
dtmNextDay = dtmNextDay + 1
Do Until Weekday(dtmNextDay, vbMonday) < 6
dtmNextDay = dtmNextDay + 1
Loop
Next n

BusinessDaysOut = dtmNextDay

End Function

Call it like so:

BusinessDaysOut([ACTIVITY_DATE], 2)

Ken Sheridan
Stafford, England
 
M

Mary

Thanks Ken! Worked like a charm!

Ken Sheridan said:
Mary:

Try this:

Public Function BusinessDaysOut(dtmDate As Date, NumberOfDays As Integer) As
Date

Dim n As Integer
Dim dtmNextDay As Date

dtmNextDay = dtmDate

For n = 1 To NumberOfDays
dtmNextDay = dtmNextDay + 1
Do Until Weekday(dtmNextDay, vbMonday) < 6
dtmNextDay = dtmNextDay + 1
Loop
Next n

BusinessDaysOut = dtmNextDay

End Function

Call it like so:

BusinessDaysOut([ACTIVITY_DATE], 2)

Ken Sheridan
Stafford, England

Mary said:
I'm able to count the number of business days between two dates, but now I
need to calculate two business days out from a date field. Can you tell me
how to do this?

The count is done in a query using this criteria that I got from another post:
Bus_Days: dhCountWorkdaysA([ACTIVITY_DATE],Date())

Is there a similar function that will display the actual date that is two
business days out?

Thanks!
Mary
Click to expand...
Click to expand...
 
Top