Date Diff for workdays in current year

B

Brian

Is there a way to calculate the number of workweeks for the current year
automatically. I want to calculate and then display the average number of
units produced per work day as of the current day. Since this will be
displayed on a splash type screen i dont want to have to enter any parameters
manually

pseudo-code

# units produced year(Now()) / # weekdays in current year(now())
 
K

KARL DEWEY

Create a table named Holidays with field Holiday - DateTime (You can add
another field for explaination if you want).
Create a second table named CountNumber with field named CountNUM - number
integer. Populate from zero to your maximum date spread.


The first query named [Past workdays].

SELECT Date()-[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((Weekday(Date()-[CountNUM])) Between 2 And 6)) AND CountNUM <=
DateDiff("d",DateSerial(Format(Date(),"yyyy"),1,1),Date())
GROUP BY Date()-[CountNUM]
ORDER BY Date()-[CountNUM] DESC;

SELECT [Past workdays].[My Dates]
FROM [Past workdays] LEFT JOIN Holidays ON [Past workdays].[My Dates] =
Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Past workdays].[My Dates] DESC;
 
K

Ken Sheridan

Another way is to create a calendar table. The following function will do
this for you. Note that, as it uses ADOX, you'll need to create a reference
to the Microsoft ADO Extensions for DDL and Security library if you've not
done so already. This is done from the Tools|References menu on the VBA menu
bar; just scroll down the list in the dialogue and select the library.

Public Function MakeCalendar(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim cmd As ADODB.Command
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute

' refresh database window
Application.RefreshDatabaseWindow

' refresh catalog
cat.Tables.Refresh

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If

End Function

To create a calendar table WeekdayCalendar of weekdays only from this year
to the end of 2015 say, call it like so:

MakeCalendar "WeekdayCalendar", #01/01/2007#, #31/12/2015#, 2,3,4,5,6

You can then return the week days in the current year with:

SELECT COUNT(*) AS WeekdayCount
FROM WeekdayCalendar
WHERE YEAR(calDate) = YEAR(DATE())
AND calDate <= DATE();

If you want to exclude holidays create a Holidays table as Karl suggests and
amend the query to:

SELECT COUNT(*) AS WeekdayCount
FROM WeekdayCalendar
WHERE YEAR(calDate) = YEAR(DATE())
AND calDate <= DATE()
AND NOT EXISTS
(SELECT *
FROM Holidays
WHERE Holidays.Holiday = WeekdayCalendar.calDate);

You can reference the query in the ControlSouirce property of a text box
control on a form with the DLookup function:

DLookup("WeekdayCount","qryWeekdaysCount")

BTW auxiliary calendar tables like the above are very useful in many
situations, particularly when you need to return dates which don't otherwise
exist in the databse, e.g. in a room reservation system where you want to
return dates when rooms are vacant, whereas the values in the tables are the
start and end of date ranges when the rooms are occupied.

Ken Sheridan
Stafford, England
 
Top