Business Days

[Guest]

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

 

[Guest]

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))))

 

[Michael Gramelspacher]

(e-mail address removed)>, (e-mail address removed)
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

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));

 

[Guest]

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

 

[Guest]

Thanks Ken! Worked like a charm!

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

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