Due date and countdown for working days

I have been trying many differnent codes/expessions within a query to setup a 
database field [DTE_TO_SCH] that will provide a due date 10 working days from 
the date entered by the user in [DTE_IC_RECVD] field. I can get it to display 
10 days from date entered but it includes the non-business days as well.

I also need to provide a countdown as to how many working days a user has 
left until Due date; such as 5 days for remaining or -5 days for over. I 
guess this second problem would be easy to answer if i knew the answer for 
the first one. My biggest problem is formatting it for working days. 

Any help is much appreciated, Thanks. 
0
Utf
11/17/2009 11:17:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
2358 Views

Similar Articles

[PageSpeed] 24

If you know the date received, why do you need to store the [DTE_TO_SCH]?

Why not use a query to do that calculation on the fly?  You could also use a 
query to get your "countdown" info.

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Jery J." <JeryJ@discussions.microsoft.com> wrote in message 
news:161B21DE-4EB3-4642-8B8F-0CC0EBB26DCB@microsoft.com...
>I have been trying many differnent codes/expessions within a query to setup 
>a
> database field [DTE_TO_SCH] that will provide a due date 10 working days 
> from
> the date entered by the user in [DTE_IC_RECVD] field. I can get it to 
> display
> 10 days from date entered but it includes the non-business days as well.
>
> I also need to provide a countdown as to how many working days a user has
> left until Due date; such as 5 days for remaining or -5 days for over. I
> guess this second problem would be easy to answer if i knew the answer for
> the first one. My biggest problem is formatting it for working days.
>
> Any help is much appreciated, Thanks. 


0
Jeff
11/18/2009 12:48:47 AM
The following functions will add working days to a date, and return the
number of working days between two dates respectively:

Public Function WorkDaysAdd(dtmDateFrom As Date, intDays As Integer) As Date

    Dim dtmDate As Date
    Dim n As Integer
    Dim intIncr As Integer
    
    ' value by which date will be incremented
    ' each day is 1 or -1 depending on sign of
    ' intDays argument
    intIncr = intDays / Abs(intDays)
    ' initialize date from which to count
    dtmDate = dtmDateFrom
    
    ' add days one by one
    For n = 1 To Abs(intDays)
        dtmDate = DateAdd("d", intIncr, dtmDate)
        ' skip weekends
        Do While Weekday(dtmDate, vbMonday) > 5
            dtmDate = DateAdd("d", intIncr, dtmDate)
        Loop
    Next n
    
    WorkDaysAdd = dtmDate
    
End Function


Public Function WorkdaysDiff(dtmStart As Date, dtmEnd As Date) As Integer

    Dim dtmDate As Date
    Dim intCount As Integer
    Dim intStep As Integer
    
    ' if end date before start date then
    ' step backwards from day prior to start date,
    ' otherwise step forwards from day following
    ' start date
    If dtmStart <= dtmEnd Then
        intStep = 1
        dtmStart = DateAdd("d", 1, dtmStart)
    Else
        intStep = -1
        dtmStart = DateAdd("d", -1, dtmStart)
    End If
    
    ' step through days from start day
    ' and count all but weekends
    For dtmDate = dtmStart To dtmEnd Step intStep
        If Weekday(dtmDate, vbMonday) < 6 Then
            intCount = intCount + 1
        End If
    Next dtmDate
    
    WorkdaysDiff = intCount * intStep
    
End Function

So you can call them in a query like this:

SELECT [DTE_IC_RECVD],
WorkdaysAdd([DTE_IC_RECVD],10) 
AS [DTE_TO_SCH],
WorkdaysDiff(Date(),WorkdaysAdd([DTE_IC_RECVD],10))
AS [DAYS REMAINING]
FROM [YourTable];

Note that each function discounts weekends, but not public or concessionary
holidays, but these could be taken into account by having a table of holiday
dates and looking up each date in this table by means of the DLookup function
within the For….Next loops, discounting the date if the DLookup function does
not return a Null.

Ken Sheridan
Stafford, England

Jery J. wrote:
>I have been trying many differnent codes/expessions within a query to setup a 
>database field [DTE_TO_SCH] that will provide a due date 10 working days from 
>the date entered by the user in [DTE_IC_RECVD] field. I can get it to display 
>10 days from date entered but it includes the non-business days as well.
>
>I also need to provide a countdown as to how many working days a user has 
>left until Due date; such as 5 days for remaining or -5 days for over. I 
>guess this second problem would be easy to answer if i knew the answer for 
>the first one. My biggest problem is formatting it for working days. 
>
>Any help is much appreciated, Thanks.

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
11/18/2009 1:01:00 AM
Reply:

Similar Artilces:

Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...

Regarding button dosen't work
Regarding button dosen't work on none of the Outlook client CRM forms. Problem is just on one machine. CRM 1.2 Try clearing the IE cache on that machine. Gill >-----Original Message----- >Regarding button dosen't work on none of the Outlook >client CRM forms. Problem is just on one machine. CRM 1.2 >. > Thanks for reply but didn't work. Any other ideas? >-----Original Message----- >Try clearing the IE cache on that machine. > >Gill >>-----Original Message----- >>Regarding button dosen't work on none of the Outlook >>client C...

Entering Todays date in an excel spreadseet
This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C56708.1CBA5520 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a need to automatically save the current date to a cell in an = excel spreadsheet every time the sheet is saved. Today() always returns the current date, so once I open the sheet on a = new day I can no longer see the date the information was last updated, = i.e. saved. Any help would be appreciated. ------=_NextPart_000_0020_01C56708.1CBA5520 Content-Type: text/html; charset="iso-8859-1&q...

Time without date
I'd like the current system time displayed in a cell when I press 'enter' Only command I know is =NOW() which displays the date and time in a cell. How do you display only the time? AM PM Doesn't matter. I've tried looking it up in help but can't but can't seem to find the answer to my question. TIA =Mod(NOW(),1) and format as hh:mm:ss -- HTH Bob Phillips "Mickey Mouse" <.> wrote in message news:428af6f8$0$5178$afc38c87@news.optusnet.com.au... > I'd like the current system time displayed in a cell when I press 'enter' &...

excel to make the days cary over month to month automaticly
I WOULD LIKE EXCEL TO IMPUT THE DATES OF CALEDAR IN THE EXAM:A CULUM IN SPRED SHET 1 THEN CARY IT TO THE SPREDSHET2 THE NEXT MONTH AND SO ON Re-post with a better description after using a spell-checker. Also drop the ALL CAPS please. Gord Dibben MS Excel MVP On Sat, 19 Apr 2008 04:07:00 -0700, GARY <GARY@discussions.microsoft.com> wrote: >I WOULD LIKE EXCEL TO IMPUT THE DATES OF CALEDAR IN THE EXAM:A CULUM IN SPRED >SHET 1 THEN CARY IT TO THE SPREDSHET2 THE NEXT MONTH AND SO ON ...

date formatting 03-22-10
I have an excel sheet which has a date in yyyy/mm/dd format saved on the internal network. other users who open the file do not see this date as the same format as the saved file. could anyone provide as to why this is happening and what can be done to correct it. thanks vandy Sounds like you have not specifically formatted the cell as yyyy/mm/dd. When you don't specifically format a date cell, Excel will look at your Windows default setting and use that, so you will see a date as yyyy/mm/dd and someone else will see it as mm/dd/yy depending on their windows setting. T...

Date input mask
I have a form that has several date input mask: 99/99/0000;0;_ . I have them auto tab to the next field. Lately after putting in all my data I copy and paste into an excel spread sheet. Then I find out that some of the dates have 208 instead of 2008. I don't know how this can happen since I have to put in 4 digits in the year. I did do some testing and once in one of the fields it did put in 208 in the year part of the year. I think I put in 01/25/2008. If I try to only put in 3 digits I get an error message and it won't let me go on. Any idea? I didn't have this pr...

Work out overtime hours for individuals
Hi All I have a question which i am hoping someone will eb able to help me with, i have a spreadsheet which contains infromation for part time hours worked during the course of a month for individuals (e.g. below) Name Start End Hours Persona 1:30 2:30 1 Persona 3:30 4:30 1 Persona 9:30 11:30 person b person b person c person c person d etc etc What i need is a formula that will allo wme to add an extra column which gives me the total hours person a worked, person be worked and so on. The original data contains multiple lines (i.e persona could have 10 lines, personb15 lines etc etc), and...

[Entourage 2004] Show me the date!
Hi, How can I force Entourage 2004 (SP2) to sort mails by received and at the same time show me the receive date instead of weekday and time? It doesn't work to disable 'Use relative dates in lists (Today, Yesterday)' in General Preferences > General. It still shows me weekday and time instead of the date. -- Regards Madsen Under the "View" menu, set "Preview Pane" to "Below List" or "None". If the Preview Pane is set so that it is "On Right", the message list will always use relative dates. I agree that it is an unfo...

Automatic reply does not work
I am trying to set Outlook to automatically reply with a template to any message with certains words in the subject line but it does not work. When I receive a message that the rule should apply to I get a popup window "Rules in Error" that says Cannot reply to message. I have tried everything I know but can't get the auto reply to send the message. Thanks for helping. For the benefit of others who may find this post (since you know the answer from another post) - it’s a bug in Outlook 2007 SP2. Best advice going forward is to make sure you have the latest upda...

simple query not working
Hi: Trying to set up a simple query, and can't figure out why its not working. Hope someone can help me think a little more clearly. Trying to link two tables: 1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode' 2)tblCodes, with fields 'SvcCode', and 'SvcDescription' Join is 'SvcCode' Query design is to show ClientID, DateofSvc, SvcCode, and SvcDescription. SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc, tblCodes.SvcCode, tblCodes.Description FROM tblSERVICES INNER JOIN tblCodes ON tblSERVICES.SvcCode = tblCodes...

Auto Correct in Outlook will not work
I have Outlook 2007 and Word 2007 and the auto correct feature will not work in Outlook or Word. I can add and see the entry in auto correct in both programs. However, when I try and use it, nothing happens. I am trying to get Fyi to turn into F.Y.I. You need to set the auto-correct options in Outlook 2007. 2007 no = longer depends on Word for its auto-correct options. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, di...

AutoNumber Reset Doesn't Work in XP
We are running Access 2000 against a database on a network drive. I have a query in this database that resets an autonumber field (jrnl_line_nbr) back to 1 when a criteria is met. I accomplish this by appending the jrnl_line_nbr field from another table that has one row with the Long Integer number 1 in it (Microsoft recommended method). It has been working flawlessly for over a year running on a Windows 98 PC. I now need to have this run from a Windows XP PC. Resetting AutoNumber no longer works. The appended row has the value 1 in it, but the following rows pick up with the la...

How to have a date range equal a particular month
What is the expression for converting multiple dates mm/dd/yyyy to the coinsiding month? It is according to how you are going to use the data but try this -- Format([YourField], "yyyymm") This will give you 200711 for this month. Format([YourField], "mmmm yyyy") This will give you November 2007 for this month. -- KARL DEWEY Build a little - Test a little "tvillare" wrote: > What is the expression for converting multiple dates mm/dd/yyyy to the > coinsiding month? Format(DateField, "yyyy-mm") as stri...

Creating a calculated field using dates in a form
I am trying to use dates from 2 fields in a form (HireDate-DOB) to calculate age. Then, using the age, I want to display a message if the age is less than 18. I think I have written a conditional statement correctly, but am unsure where to place it so that the message displays when the condition is met. I am totally new to Access 2007 and VB. You did not say what the content of your 'conditional statement' was and how you plan on using it. What has HireDate to do with it? Is it you want to see if their age is less than 18 on the date of hiring? Then this will c...

how to uninstall MS office 2007 when the 60 day trial ran out
My ms office 2007 trial ran out and tried to uninstall in add/remove program...no luck and now it's not listed in add/remove program.....because of this It won't let me re-install or uninstall office 2007....how do i uninstall it so i can install office 2003 How to uninstall the 2007 Office system if you cannot uninstall it by using the Add or Remove Programs feature http://support.microsoft.com/kb/928218/en-us -- Mary Sauer http://msauer.mvps.org/ "fleblanc0253" <fleblanc0253@discussions.microsoft.com> wrote in message news:8381FC66-D6CA-41E4-BE91...

Office 2007 trial expired immediately due to prior Office 2003
I attempted to install a trial version of Office 2007 Pro, and the activation wizard said "Your trial period expired on December 31, 2002". This PC is Windows XP machine, new in about year 2005, where the machine already had an expired trial version of Office 2003 installed. I was using the expired Office 2003 trial as a file viewer and never activated the 60-day trial period for it. When installing the Office 2007 Pro trial, I selected the option to remove the older version of Office, which worked successfully. Now when I start any Office app, the activation wizard say...

Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...

Multiple user accounts not working properly
I am running Windows XP and have three user account logins. I was also running Outlook Express and set up three email accounts (each on their own login).. the first two email setups worked great. But the third one wasnt recognizing my password as valid. I Contacted the ISP. Walked through setup. Still didnt work. ISP instructed me to access mail via the internet. That worked. ISP said that proves the account is valid. Now, I just upgraded to Outlook 2003. Same problem with the third email user account. Funny thing is that this "3rd" email account is one of the tw...

iFrame and dates
Hi Guys, I'm trying to fill in a crmForm that has a date type field, from an iFrame. In the codebehind of the iFrame's aspx I'm using "parent.crmForm.all.<fieldname>.value=data where data is a CrmDateTime value, but the field does not appear. Please help, Thanks, Paulo Fonseca ...

working with caret
hi! is it possible to send data to the caret ? i mean, lets assume we have 100 editboxes, the user clicks on one of the them, i want programmaticaly be able to send a letter to the selected editbox without knowing wich one(editbox) it is. climax! wrote: > hi! > > is it possible to send data to the caret ? i mean, lets assume we have > 100 editboxes, the user clicks on one of the them, i want > programmaticaly be able to send a letter to the selected editbox > without knowing wich one(editbox) it is. > // From MSDN Q145616 CEdit* CMainFrame::IsEditInFocus() { TCHAR s...

Scroll box does not work
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I have a document that has scroll boxes sent to me, and I cannot get them to work, all I get is the picture. The odds are that ActiveX Controls were innappropriately used in the creation of the document. ActiveX isn't supported by the Mac OS. -- Regards |:>) Bob Jones [MVP] Office:Mac <RJ@officeformac.com> wrote in message news:59bae0d6.-1@webcrossing.JaKIaxP2ac0... > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power > PC I have a document that has scroll ...

User cannot log in after Outlook 2003 installation
Hello, We are experiencing the same issues as others (http://www.mcse.ms/message1591947.html) but moving the mailbox does not clear up our issue: Users who receive the Outlook 2003 software cannot log into their mailboxes the next day...although OWA works just fine. Below have been my troubleshooting steps: 1. Had user log into her peer's workstation and create a new profile - same error. 2. User can access OWA just fine. 3. Move mailbox to new server - same error. 4. Removed Mailbox Rights and readded them. 5. Created pst, disconnected mailbox, created new mailbox on different ...

I type a date (1/05) in a cell, press enter, it reads ####.Why?
I'm entering data inMicrosoft Excel and am fairly new to Excel. I'm entering dates in cells in a worksheet and typred in a date and pressed enter and all it will do is read #####. Why does it do this and how do I get the computer to recognise the actual numbers of the date I entered? erg Probably the cell is not wide enough. -- Kind regards, Niek Otten Microsoft MVP - Excel "KIWI" <KIWI@discussions.microsoft.com> wrote in message news:D1F07478-F035-4DDF-8F77-5FC5A070155F@microsoft.com... | I'm entering data inMicrosoft Excel and am fairly new to Excel. I...

How can I add the current date to the Inventory Received Report?
I'm trying to add the current date to the Inventory Received Report that you can print out when you receive in a purchase order. The specific file I'm working on is "PORcv.xml". I'm able to edit text in the report, but I don't know how to capture the system date and get it to print out on the report. Is there a way to do this? TIA mbehm@hutchtel.net Does anybody have any suggestions for me? "Mike B." wrote: > I'm trying to add the current date to the Inventory Received Report that you > can print out when you receive in a purchase orde...