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: jysharp2003@yahoo.com Oct 20 2019, 07:16 PM

Hi,
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 http://www.databasedev.co.UK/calculate_work_days.html and http://access.mvps.org/access/datetime/date0006.htm

Posted by: jysharp2003@yahoo.com 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. https://support.office.com/en-us/article/Access-Functions-by-category-B8B136C3-2716-4D39-94A2-658CE330ED83

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 https://www.UtterAccess.com/forum/index.php?showtopic=1349593&pid=1349593&st=0&#entry1349593 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:

CODE
' 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
    Wend
    
    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: jysharp2003@yahoo.com 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 .. https://www.UtterAccess.com/forum/index.php?s=&showtopic=1349593&view=findpost&p=1349593

I makes use of a holiday table.

Not that I am partial smile.gif