[X]My Assistant

[jysharp2003@yaho...]

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

[June7]

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

This post has been edited by June7: Oct 20 2019, 07:34 PM

[jysharp2003@yaho...]

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?

Attached File(s)

 Snag_36c8bd0.png ( 40.53K )Number of downloads: 2

 

[June7]

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/Ac...A2-658CE330ED83

[GroverParkGeorge]

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 one or two are available here in addition to the links previously offered.

[arnelgp]

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;

[jysharp2003@yaho...]

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

[MadPiet]

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?

[dmhzx]

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().

[datAdrenaline]

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

I highly recommend this one .. https://www.UtterAccess.com/forum/index.php...t&p=1349593

I makes use of a holiday table.

Not that I am partial