Hello Hanz / All
i came across one of your very useful code snippets.
Add number of working days to a date excluding holidays and weekends.
it works perfectly.
The only thing i cannot seem to get working as a Group/Sort
it treats it like text and not a date.
i.e. 30th March comes after 1st April
I have tried formatting it but to no avail.
is there a workaround ?
many thanks
nick
grouping / sorting on an Expression - workingdays
-
- NewLounger
- Posts: 3
- Joined: 06 Apr 2022, 12:36
-
- Administrator
- Posts: 78474
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: grouping / sorting on an Expression - workingdays
Welcome to Eileen's Lounge!
Could you tell us which code you're using?
Could you tell us which code you're using?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 06 Apr 2022, 12:36
Re: grouping / sorting on an Expression - workingdays
This is th ecode
Code: Select all
Public Function AddWorkDays(StartDate As Variant, NumDays As Long) As Variant
'....................................................................
' Name: AddWorkDays
' Inputs:
' StartDate As Variant/Date
' NumDays As Long
' Returns:
' Variant/Date
' It uses a table named tblHolidays with a field named HolidayDate.
'....................................................................
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim dtmCurr As Date
Dim lngCount As Long
AddWorkDays = Null
If Not IsDate(StartDate) Then
Exit Function
End If
On Error GoTo ExitHandler
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
lngCount = 0
dtmCurr = StartDate
Do While lngCount < NumDays
dtmCurr = dtmCurr - 1
If Weekday(dtmCurr, vbMonday) < 6 Then
rst.FindFirst "[HolidayDate] = #" & Format(dtmCurr, "dd/mm/yyyy") & "#"
If rst.NoMatch Then
lngCount = lngCount + 1
End If
End If
Loop
AddWorkDays = dtmCurr
ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Function
-
- Administrator
- Posts: 78474
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: grouping / sorting on an Expression - workingdays
Thanks! The problem is that the function takes blank start dates into account and so by necessity returns a Variant, not a Date type value.
If you are certain that the date field will always be populated, you could use this version:
If you are certain that the date field will always be populated, you could use this version:
Code: Select all
Public Function AddWorkDays(StartDate As Date, NumDays As Long) As Date
'....................................................................
' Name: AddWorkDays
' Inputs: StartDate As Variant/Date
' NumDays As Long
' Returns: Variant/Date
' Note that this function has been modified to account for holidays.
' It requires a table named tblHolidays with a field named HolidayDate.
'....................................................................
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim dtmCurr As Date
Dim lngCount As Long
On Error GoTo ExitHandler
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
lngCount = 0
dtmCurr = StartDate
Do While lngCount < NumDays
dtmCurr = dtmCurr + 1
If Weekday(dtmCurr, vbMonday) < 6 Then
rst.FindFirst "[HolidayDate] = #" & Format(dtmCurr, "mm/dd/yyyy") & "#"
If rst.NoMatch Then
lngCount = lngCount + 1
End If
End If
Loop
AddWorkDays = dtmCurr
ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Function
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 06 Apr 2022, 12:36
Re: grouping / sorting on an Expression - workingdays
**** YOU ABSOLUTE STAR ****
thank you so much - that is perfect
thank you so much - that is perfect