Calculate Days Elapsed in Weekdays

  • Thread starter Thread starter Williams
  • Start date Start date
W

Williams

I have a [ProposalDate] field and an [ApprovalDate] field. I need to find
out how much time elaspsed between the two dates. If I enter:
[approvaldate]-[proposaldate] it give me the number of days, but how to I
restrict this to count only weekdays? thanks.
 
What about holidays? Here is a function that returns the number of working
days between two dates. The count includes both the beginning date and the
end date. If you dont want to exclude holidays in the count, see comments in
the code on what to take out. To exclude holidays from the count, create a
holidays table with the date of the holiday. In mine, it is the date and the
description. You can either use the names in my code or change the names to
those you use:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1

'Remove this code if you don't use a holiday table:
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function
 
Back
Top