How to calculate dates based on a 5 day week

[Breeves22]

Hi all,

I was wondering if it was possible to use Access 2000 to auto calculate a date in a field operating under a 5 day working week

Thankyou in advance

[TheSmileyCoder]

Yes.

Give us some more information to work with, we can write you a general answer, but its much easier to write something specific. Give us an example of what you want to accomplish.

[Breeves22]

Thankyou for your time.

What i am trying to do is create a database to monitor all our active cases. My company works on a 5 day week (Monday to Friday) and the database needs to reflect that. For example once we have drawn up a schedule of works we send it to the local council for approval and they have 15 days to return it to us with their comments. Now if i use DateAdd to just add 15 days from the date sent it will come up with the wrong date because of our only counting week days.

[TheSmileyCoder]

Here you go, I threw this together for you:

Public Function addWorkDays(DateInput As Date, intDays As Integer) As Date
    'Function that only adds days if they are workdays (monday-friday)
    'Holidays are not excluded.
    'Code by: TheSmileyOne
    'First presented on: www.BYTES.com
    ' Code my be freely used, redistributed and eaten. 
    ' If you value my work enough to use it, 
    ' please value it enough to leave my name in it.
 
 
    'Setup a working date
        Dim myDT As Date
        myDT = DateInput
 
    'Setup number of days remaining to add
        Dim intDaysRemain As Integer
        intDaysRemain = intDays
 
    'Now add days 1 at a time,
    ' while counting down intDaysRemain,
    ' but only if day is a working day
        Do Until intDaysRemain = 0
            myDT = DateAdd("d", 1, myDT)
            Select Case Weekday(myDT)
                Case vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday
                    intDaysRemain = intDaysRemain - 1
                Case vbSaturday, vbSunday
                    'Don't modify intDaysRemain
            End Select
        Loop
    'Return answer
        addWorkDays = myDT
 
End Function

Now this will only exclude saturday/sunday, not holidays. If you need to exclude holidays as well, let me know, and I will work something out for that (the solution would involve you manually entering holidays into a table.)

[JDDDDD]

Hiii

Im trying to do the same. could you please tell me where to enter the code

[TheSmileyCoder]

The code itself, should go in a seperate access module. In the main access object browser (the datebase window) select Modules, then New. Simply copy & paste the code into the window there.

Wherever you need to use it, you can simply call the function, like any other function. If per say its a calculated control it could like this in the controlsource:

=addWorkDays([OriginalDate];5)

[JDDDDD]

ok i got where to put it sorted dudes. but when i try your function code it just comes up with
The visual basic model contains a syntax error. check the code and then recompile it.

i copied and pasted your code Smiley but changed Original date to Date courswork given and changed the 5 to 15

[Breeves22]

Unfortunatley i am having the same problem as JDDDDD. I am unable to get =addworkdays the code to work on my form. I tried adapting it a little to make it similar to the way i use a DateAdd but still no luck. any help would be greatly appreciated.

[TheSmileyCoder]

Sometimes when I copy code blocks from here, the line numbers get copied with, they of course need to be removed. Im wondering if maybe you deleted to much/little.

I retried the code, this time in Access 2007, and it still works fine. I made a small modification to the way I call it from within a form, so that it could more smoothly handle cases where you had not yet entered your original date.

=IIf(IsNull([OriginalDate]);"";AddWorkDays([OriginalDate];5))

Depending on regional settings you may need to replace ; with ,

Again, this is the syntax to be used in the controlsource of a textbox on a form, and it would give a CALCULATED field to display on your form.(I.e. the info is not stored in your table)

If your using it in another way, provide more details (such as code if relevant).

[JDDDDD]

Still not working :'( this time it says you omitted and operand or operator, you entered an invalid comma or character or you entered text without surrounding it in quotation marks. I copied the code exactly. then only changed Original date to read DateCourseworkGiven.

The code i used before when doing a 7 day week was =DateAdd("d",15,[DateCourseworkGiven]) and that works fine for a full week but i want to base it of university days so i got an accurate image of when i need to do things by

[TheSmileyCoder]

I don't know what to say, as its working fine for me. Best I can offer is that you take a screenshot, showing exactly where/how you are using it, and then post it here.

[JDDDDD]

could it be because i am using access 2000. Maybe that version doesnt support what i am trying to do

[TheSmileyCoder]

I can't say for sure, since I dont have access 2000, but there is no special references being used in the code, and its all simple enough code. My guess is still that your using it some odd place, or with incorrect syntax. Thats why im asking for a screenshot, as an image can say more then a 1000 words :P

[JDDDDD]

ok thanks dude. i will try and post a screenshot of my form in the morning. Will try again tonight to get it working and fingers crossed it will work now!

[JDDDDD]

OK Dude. Please find attached a scheen shot showing my setup and the code.

What im trying to do is imput the date each action occurs and then have it automatically update the field next to it with the date the next action should be done by. But i only want to use weekdays cause thats what uni runs off savvy?

[TheSmileyCoder]

I don't know what the issue is. I've tested the code and it works, so you must be doing something wrong, or misunderstood something.

You can upload the database here and I can look at it, other then that I have no more to offer.

[JDDDDD]

Its ok dude. managed to sorts it out. I changed the ; to a , and it works perfectly. must be some random thing about access 2000

[TheSmileyCoder]

I hate to say I told you so....but.....

TheSmileyOne Post #9
Depending on regional settings you may need to replace ; with ,

[Breeves22]

Thanks smiley one.

your help with this problem was greatly appreciated.

[JDDDDD]

yo guy totally. you rulez. such a help now i can gets me database to tell me when all me coursework needs to be done 4.

cheerz dudez