Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Convert Number Of Days (number) Into Future Date Only Workdays

Posted by: Oct 20 2019, 07:16 PM

Under the gun here. What is syntax to result in a future date counting only Workdays?
Came close with
SELECT [CountLOB]/20 AS NumDays, metric_LOB_Librarian_unprocessedPRE.Group, metric_LOB_Librarian_unprocessedPRE.[Librarian Assigned], Now()+[NumDays] AS Est_Complete
FROM metric_LOB_Librarian_unprocessedPRE;
Trying Datediff but can't seem to get the right stuff.
Any help would be great. Thanks

Posted by: June7 Oct 20 2019, 07:27 PM

This requires a VBA custom function to exclude weekends and holidays. This is a common topic and many examples are out there.
You can include calculations for specific holidays in the procedure or have a Holidays table to lookup dates.
For a start review and

Posted by: Oct 20 2019, 08:07 PM

Thanks and I will give a look.
Too bad I can just find the insert syntax for Datediff to what I have now.
I have it resolving to future date including weekends. Dang.
Anybody else? smile.gif


Posted by: June7 Oct 20 2019, 08:42 PM

Now() includes time components. If you only want mm/dd/yyyy then use Date().

What else are you looking for? How to use DateDiff() function? Again, many resources out there.

Posted by: GroverParkGeorge Oct 20 2019, 10:39 PM

Unfortunately, there's no alternative to using a function. There are several other examples of functions that calculate WORKDAYS, which is generally taken to mean Monday through Friday for most cultures. That's what you need here. Another good or two are available here in addition to the links previously offered.

Posted by: arnelgp Oct 21 2019, 01:12 AM

add this function in a Module in VBE:

' arnelgp
Public Function fncDateAdd(ByVal dte As Date, ByVal days_to_add As Integer) As Date

    While days_to_add > 0
        dte = DateAdd("d", 1, dte)
        If Instr("Sat/Sun", Format(dte, "ddd")) = 0 Then
            days_to_add = days_to_add - 1
        End If
    fncDateAdd = dte

End Function

your query:

SELECT [CountLOB]/20 AS NumDays, metric_LOB_Librarian_unprocessedPRE.Group, metric_LOB_Librarian_unprocessedPRE.[Librarian Assigned], fncDateAdd(Date, [NumDays]) AS Est_Complete
FROM metric_LOB_Librarian_unprocessedPRE;

Posted by: Oct 21 2019, 09:14 PM

Super post!
I started Access too late in life. Getting to you all's level I probably be on me death bed. Cheers

Posted by: MadPiet Oct 21 2019, 09:52 PM

Can't you just use a Calendar table? Do something like take TOP 1 on a Calendar table when you remove the weekends and holidays? (or you do you need something like ROW_NUMBER() to do that?

Posted by: dmhzx Oct 22 2019, 03:49 AM

And if you want to exclude Public Holidays, you just need a Holiday table, and a modified version of arnelgp 's code .

Basically if the day you're looking at is a weekend or a holiday, don't count it as a day)

And to emphasise another point do NOT use Now() for this unless you really do want to include the time in your calculations. Use Date().

Posted by: datAdrenaline Oct 23 2019, 01:02 PM

You have to use a VBA function to get accurate results.

I highly recommend this one ..

I makes use of a holiday table.

Not that I am partial smile.gif