Due Dates in (Work/Business Days)

[SchuhTL]

I need a way to group items by date depending when they are due(work days). For example, I need to look at the [current date] and compare to a field called [customer want date]. I then need to group items: Due Today, Due in 1 Business Day, Bue in 2 Business Days, etc.... How can I add business days to the current date?

 

[lblastick]

Is it office 2000? In access 2000 there is a NETWORKDAYS and a WORKDAY function that would seem to be a help.

NETWORKDAYS returns the number of whole working days between a start and end date, excluding weekends and any identified holidays.

If this function returns the #NAME? error value, you may need to install msowcf.dll.

Syntax

NETWORKDAYS(start_date,end_date,holidays)

Start_date is a date that represents the start date.

End_date is a date that represents the end date.

Holidays is an optional range of one or more days (holidays) to exclude from the working calendar.

WORKDAY
Returns a number that represents a date that is the indicated number of working days before or after start_date, excluding weekends and specified holidays.

Syntax

WORKDAY(start_date,days,holidays)

Start_date is a date that represents the start date.

Days is the number of nonweekend and nonholiday days before or after Start_date (negative values for days before).

Holidays is a range of cells that represent dates to exclude from the working calendar.

 

[MichaelRed]

but NetWorkDays is an Excel Function, so you would need to reference the Excel Library. Then, the Holidays need to be specified in the call, which is QUITE inconvenient.

look at faq181-261 it is a more complete soloution - and it does not require the Excel Library.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[raskew]

Here are two functions, each taking a different approach, to determine workdays (Mon - Fri).

Function CalcWkDays(dteStartDate As Date, dteEndDate As Date) As Integer
'input: (from debug window) ? CalcWkDays(#01/01/01#, #07/01/01#)
'output: 129

Dim X As Integer

X = DateDiff("d", dteStartDate, dteEndDate) _
- 2 * DateDiff("ww", dteStartDate, dteEndDate) _
+ IIf(WeekDay([dteStartDate]) = 7, 1, 0)
CalcWkDays = X
End Function

Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date) As Integer
'input: (from debug window) ? CalcWkDays2(#01/01/01#, #07/01/01#)
'output: 130
'NOTE:As written,this counts both start and end dates.

Dim n As Integer

n = 0

Do While dteStartDate <= dteEndDate

'17 in the following expression represents the numeric
'days of week for Sunday(1) and Saturday (7)

n = n + IIf(InStr(&quot;17&quot;, WeekDay(dteStartDate)) = 0, 1, 0)
dteStartDate = dteStartDate + 1

Loop

CalcWkDays2 = n
End Function