UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Convert Number Of Days (number) Into Future Date Only Workdays, Access 2010 Web    
 
   
jysharp2003@yaho...
post Oct 20 2019, 07:16 PM
Post#1



Posts: 42
Joined: 23-January 19



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
Go to the top of the page
 
June7
post Oct 20 2019, 07:27 PM
Post#2



Posts: 971
Joined: 25-January 16



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

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
jysharp2003@yaho...
post Oct 20 2019, 08:07 PM
Post#3



Posts: 42
Joined: 23-January 19



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
Attached File(s)
Attached File  Snag_36c8bd0.png ( 40.53K )Number of downloads: 2
 
Go to the top of the page
 
June7
post Oct 20 2019, 08:42 PM
Post#4



Posts: 971
Joined: 25-January 16



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

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Oct 20 2019, 10:39 PM
Post#5


UA Admin
Posts: 36,085
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Oct 21 2019, 01:12 AM
Post#6



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


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;


--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
jysharp2003@yaho...
post Oct 21 2019, 09:14 PM
Post#7



Posts: 42
Joined: 23-January 19



Super post!
I started Access too late in life. Getting to you all's level I probably be on me death bed. Cheers
Go to the top of the page
 
MadPiet
post Oct 21 2019, 09:52 PM
Post#8



Posts: 3,361
Joined: 27-February 09



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?
Go to the top of the page
 
dmhzx
post Oct 22 2019, 03:49 AM
Post#9



Posts: 7,115
Joined: 22-December 10
From: England


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

Go to the top of the page
 
datAdrenaline
post Oct 23 2019, 01:02 PM
Post#10


UtterAccess Editor
Posts: 18,006
Joined: 4-December 03
From: Northern Virginia, USA


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 smile.gif

--------------------
Brent Spaulding | datAdrenaline | Microsoft MVP 2007 - 2019
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st November 2019 - 07:23 PM