Help with Workdays function (VBA) from Microsoft website

[GregTheSquarePeg]

Hi there,



I would like to use the Workdays function code from Microsofts website. For the most part, it works just fine. However, when looking at certain dates, the function is returning incorrect results.
I'm calculating delivery lead time - between an order date, and delivery date (I'm excluding the day of order, but including the day of delivery). For some reason the function is returning a value of '-2' for 02/08/2013 to 05/08/2013. I recognise that these dates span from Fri-Mon, so the correct lead time value should be '1'.
The function seems to be working fine for the majority of other dates, it just seems to be failing across a few certain date ranges. I've checked the 'Holidays' table being passed to the function, and all is in order there.

Does anybody have experience using this function, or an idea why this may occur?

(edit: I only know very basic stuff in VBA, so help offered in 'laymans' terms would be great)

Any help appreciated,

Gregg

The MS function to count weekdays:

Public Function Weekdays(ByRef startDate As Date, _
    ByRef endDate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate)
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

The MS function to get the workdays:

Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function

[June7]

Are 02/08/2013 and 05/08/2013 dd/mm/yyyy? Access expects mm/dd/yyyy. If you are using international dates, review http://allenbrowne.com/ser-36.html

Why would the lead time be 1 - there are 4 days (inclusive for ends) between the 2nd and 5th.

[akhmadahdiyat]

the lead time should be 1 because he want it not to include the day of delivery (friday) and weekends (saturday and sunday), so it count only monday (5th august)

[akhmadahdiyat]

is there any holiday between 2nd and 5th august ?

[June7]

Only if you put one in your holidays table.

Have you step debugged? Refer to link at bottom of my post for debugging guidelines.

[GregTheSquarePeg]

Thanks both for the suggestions.

June7 - I am in the UK, so my system dates are dd/mm/yyyy. The code is calculating 90% of the dates correctly. Is it possible the date formats could really be causing the problem?

akhmadahdyat - the Holidays table is correct, with none over that period.

I've attached some sample data - with the modules too. As you can see, most of the dates listed calculate correctly (remembering that I'm excluding the OrdDay but including the DelivDay), it's just some seemingly random ones that don't.

Any help appreciated.
Gregg

[June7]

All dates in tables are showing as mm/dd/yyyy.

I tested your function in the VBA Immediate window:

?Workdays(#8/2/2013#,#8/5/2013#)
1

As you can see, it works with U.S. date.

I also ran an UPDATE on the table and it worked.

Suggest you try Allen Browne's code.

[Missinglinq]

...Is it possible the date formats could really be causing the problem...

Access is US-centric; if a date is ambiguous, such as 02/08/2013, it can, indeed, cause problems! 02/08/2013 cab be interpreted as February 8, 2013 or as August 2, 2013, and this confuses the Access gnomes no end!

A non-ambiguous date would be one such as 13/8/2013 or 21/8/2013; there is no 13th month or 21st month, so Access knows that the format here is dd/mm/yyyy, i.e. it is not ambiguous!

Look at Allen Browne's article on handling this! He is recognized as one of the leading Access developers in the world, and, more importantly, here, he resides down under, in the Perth!

Linq ;0)>

[GregTheSquarePeg]

Thanks guys. I get it now... the reason it's working for most of the lines is that those dates can only be dd/mm/yyyy, whereas some of them could also be interpreted as mm/dd/yyyy - so in those cases, Access does the latter.

Cheers - now I understand it, I'll hopefully be able to work the solution.

Thanks again all,
Gregg

[GregTheSquarePeg]

Okay... I may have been a bit over ambitious hoping to resolve the problem so easily. I've read Allen Browne's piece on dates, and also a very long and interesting thread on date handling in the UtterAccess forum.
However - although I now feel somewhat enlightened, I'm still held back by my more basic understanding of VBA, and the different format/date format conversions in SQL.

Going on what I've read in the UA forum - I'm thinking it may be best to convert my dates to yyyy/mm/dd (or ISO, as I now understand it to be), but even with the dates converted to this format, the Workdays function still returns results for mm/dd/yyyy.

I'm guessing this is because the 'Format' function is merely changing the appearance of the date, rather than the date itself? If so, is anybody able to help me out with an expression I can use to get the date into a genuine yyyy/mm/dd please?

Many thanks,
Gregg

[June7]

I am in U.S. so dates are not in issue for me. The dates in your table show as U.S. dates for me - 7/30/2013 to 8/30/2013. Not sure I can advise you further. I would have to change computer locale settings and experiment.

Are you aware that date/time is actually stored as a double number? http://support.microsoft.com/kb/130514

How would the yyyy/mm/dd format help? Seems that any manipulation that would return correct yyyy/mm/dd could just as well return correct mm/dd/yyyy for use by the functions.

[GregTheSquarePeg]

Yes, I've read about dates being stored as double type - so maybe if I have an extra field with the same date, but in it's basic double number format - the Workdays function may calculate correctly. I'll give it a shot...

The stuff I read about using yyyy/mm/dd format was here:

www.utteraccess.com/forum/Convert-UK-date-format-t1685864.html

Thanks for your help.
Gregg

[June7]

The issue is the same as with formatting for yyyy/mm/dd.

If you want to allow users to enter date criteria as dd/mm/yyyy, then that is what might need to be manipulated into mm/dd/yyyy structure for code to process.

This of course assumes your locale system is set to U.K. and all date values in records were consistently input as dd/mm/yyyy. Because of the locale setting, Access should have saved the correct value representing the U.S. date, even though everyone sees U.K. date.

BTW, even here in U.S., we have to be consistent with entering dates in mm/dd/yyyy order. If an employee born and raised outside U.S. absentmindedly falls back on habit of dd/mm/yyyy entry, I would face issue of bad data because Access would accept ambiguous dates - 2/8/2013 is just as valid as 8/2/2013. Then if they entered dd/mm/yyyy as filter criteria, of course the results will be unsatisfactory.

[GregTheSquarePeg]

Thanks for the further explanation June7 - it seems handling dates anywhere in the world can be a challenge!

Just to tie this thread off - I thought I'd better post the solution to my problem, in the form of an amendment to the VBA code itself. With these changes made, the function now works perfectly.

Kindly provided by 'Gustav' on the UA forum:

This is typical US code with no understanding of an outside world.
Change these line to:

strWhere = "[Holiday] >= #" & Format(startDate, "yyyy\/mm\/dd") & "# " & _
        "AND [Holiday] <= #" & Format(endDate, "yyyy\/mm\/dd") & "#"

and this you may kill:

' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

/gustav

Hopefully this will help anybody else using the same code or similar.

Gregg

[June7]

An alternate structure is:

strWhere = "[Holiday] BETWEEN #" & Format(startDate, "yyyy\/mm\/dd") & "# AND #" & Format(endDate, "yyyy\/mm\/dd") & "#"

The BETWEEN AND operator is inclusive of the date range.