Need to Add 12 working Days...

N

Number11

Member
Local time
Today, 23:00
Joined
Jan 29, 2020
Messages
619
So i have this within a form field...

DateAdd("ww",2,Date())

which by default sets the date to 10 working days from today, i need to have this set as 12 working Day which is Monday - Friday ... how would i do this
 
Last edited:
Minty said:
Do you want to also include holidays?
There are lots of threads on here about working days, with and without holidays.
Have a search e.g https://www.access-programmers.co.u...ding-we-hols-function-is-mixing-dd-mm.314256/
Click to expand...
Thanks took a look at that thread and doesn't seem to be what i am looking for ..

I have a Data entery form and need to auto populate the date today plus 12 working days, and if the 12 day is a UK Bank Holiday then show the next working date..
 
The function code posted in that thread allows you to add (or subtract) a number of working days and takes account of holidays if you include a table with the holiday dates in it.

You give a start date, a number of days to add and it returns a date based on those criteria.

I'm not sure what else you might need?
 
Number11 said:
Thanks took a look at that thread and doesn't seem to be what i am looking for ..

I have a Data entery form and need to auto populate the date today plus 12 working days, and if the 12 day is a UK Bank Holiday then show the next working date..
Click to expand...
From your explanation it sounds like you have a Data Entry Form with Columns as follows:

DateFrom Day1 Day2 Day3 Day4 etc - Day12

Is this correct?
 
mike60smart said:
From your explanation it sounds like you have a Data Entry Form with Columns as follows:

DateFrom Day1 Day2 Day3 Day4 etc - Day12

Is this correct?
Click to expand...
Thanks for your reply,

i have just one date field - called Job Date correctly I have this set as the default "DateAdd("ww",2,Date())" which shows me date 9today0 plus 10 working days.. need to now be 11 working days, no getting dates for weekend
 
Number11 said:
Thanks for your reply,

i have just one date field - called Job Date correctly I have this set as the default "DateAdd("ww",2,Date())" which shows me date 9today0 plus 10 working days.. need to now be 11 working days, no getting dates for weekend
Click to expand...
Can you upload your database?
 
Number11 said:
Thanks for your reply,

i have just one date field - called Job Date correctly I have this set as the default "DateAdd("ww",2,Date())" which shows me date 9today0 plus 10 working days.. need to now be 11 working days, no getting dates for weekend
Click to expand...

Simply pass ( Date(), 11 ) into that function it will return the date 11 working days from now.
 
Minty said:
Simply pass ( Date(), 11 ) into that function it will return the date 11 working days from now.
Click to expand...
so when you say pass into that function - what function do you mean? and how using the original method
 
The code in the other thread is a function.
Paste that into a new code module - save it as modFunctions.

Now in your form you can use the function just like any other inbuilt access function.

aWorkdays(Date(),11)

And it will magically add 11 working days to the current date.
 
Minty said:
The code in the other thread is a function.
Paste that into a new code module - save it as modFunctions.

Now in your form you can use the function just like any other inbuilt access function.

aWorkdays(Date(),11)

And it will magically add 11 working days to the current date.
Click to expand...
this is working but its still selecting a Saturday as the date today 22.01.22..

function i am using

Public Function wdateadd(Startdate As Date, wdays As Integer)
Dim h As Integer
Dim w As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [BDate] FROM BankHoliday", dbOpenSnapshot)
w = 0
h = 0
Do Until h = wdays
mydate = DateAdd("d", w, Startdate)
rst.FindFirst "[BDate] = #" & mydate & "#"
If Weekday(mydate) <> vbSunday And Weekday(mydate) <> vbSaturday Then
If rst.NoMatch Then
h = h + 1
End If
End If
w = w + 1
Loop

wdateadd = DateAdd("d", w, Startdate)

End Function
 
Your function as written won't compile, MyDate isn't declared.
After fixing that, I am getting a different result to you - from the immediate window;

?wdateadd (date(),11)
21/01/2022

What do you get if you type the following into the immediate window?

? vbsaturday
7
 
Minty said:
Your function as written won't compile, MyDate isn't declared.
After fixing that, I am getting a different result to you - from the immediate window;

?wdateadd (date(),11)
21/01/2022

What do you get if you type the following into the immediate window?

? vbsaturday
7
Click to expand...
ok sorry i have change the criteria now to 12 working days will then falls on a sat not the monday 24.01.02
 
Your code for checking the weekend days has a fundamental flaw I think.

Try this (it also fixed a problem with your holiday lookup that will fail in the future).
It's not fixed but should show you how to work out what is wrong. Look into the immediate window after typing
? wdateadd(Date(),12)


Code: 
Public Function wdateadd(Startdate As Date, wdays As Integer)
    Dim h As Integer
    Dim w As Integer
    Dim rst As DAO.Recordset
    Dim DB As DAO.Database
    Dim MyDate As Date
    
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("SELECT [BDate] FROM BankHoliday", dbOpenSnapshot)
    w = 0
    h = 0
    Do Until h = wdays
        MyDate = DateAdd("d", w, Startdate)
        rst.FindFirst "[BDate] = #" & Format(MyDate, "yyyy-mm-dd") & "#"   'Added formating to get correct results
        Debug.Print MyDate, Weekday(MyDate), w, h
        If rst.NoMatch Then
            If Weekday(MyDate) <> vbSaturday And Weekday(MyDate) <> vbSunday Then
                h = h + 1
            End If
        End If
        w = w + 1
    Loop

    wdateadd = DateAdd("d", w, Startdate)

End Function
 
Minty said:
Your code for checking the weekend days has a fundamental flaw I think.

Try this (it also fixed a problem with your holiday lookup that will fail in the future).
It's not fixed but should show you how to work out what is wrong. Look into the immediate window after typing
? wdateadd(Date(),12)


Code: 
Public Function wdateadd(Startdate As Date, wdays As Integer)
    Dim h As Integer
    Dim w As Integer
    Dim rst As DAO.Recordset
    Dim DB As DAO.Database
    Dim MyDate As Date
   
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("SELECT [BDate] FROM BankHoliday", dbOpenSnapshot)
    w = 0
    h = 0
    Do Until h = wdays
        MyDate = DateAdd("d", w, Startdate)
        rst.FindFirst "[BDate] = #" & Format(MyDate, "yyyy-mm-dd") & "#"   'Added formating to get correct results
        Debug.Print MyDate, Weekday(MyDate), w, h
        If rst.NoMatch Then
            If Weekday(MyDate) <> vbSaturday And Weekday(MyDate) <> vbSunday Then
                h = h + 1
            End If
        End If
        w = w + 1
    Loop

    wdateadd = DateAdd("d", w, Startdate)

End Function
Click to expand...
ok thanks i still get the date as 22.01.2022 which is a saturday
 
Yes - can you not see why in the debug data?
It's not picking up the weekend days correctly. So your check for those appears to not be functioning.

I would change the logic to check for those days, not that it isn't those days.
 
There is another function here from Arnelgp

Solved - subtract number of WORK DAYS in report expression

Hello, I have a report that I need to minus certain amount of workdays from a preferred date. This is what I have that is working, but it is including weekends. =DateAdd("d",-[LeadTime],[CustomerPreferredDate]) I need it to only use working days. (I'll tackle the whole holiday thing later)...
www.access-programmers.co.uk
 
Minty said:
Yes - can you not see why in the debug data?
It's not picking up the weekend days correctly. So your check for those appears to not be functioning.

I would change the logic to check for those days, not that it isn't those days.
Click to expand...
Thanks i have no idea on how to get this to work so i am stuck, will take a look at the other function but thats seems to be something different than what i need
 
ok so got this now working with this function code

Public Function addDays(ByVal iDays As Integer, ByVal dteStart As Date) As Date
Dim i As Integer
Dim m As Integer
m = Abs(iDays)
While m > 0
If iDays < 0 Then
dteStart = dteStart - 1
Else
dteStart = dteStart + 1
End If
If InStr(1, "sat/sun", Format$(dteStart, "ddd")) <> 0 Then
Else
m = m - 1
End If
Wend
addDays = dteStart
End Function


call by

=addDays(+12,Date())

just now need to add the check for Bank holidays?
 
You must log in or register to reply here.

Similar threads

C
Query Help on "Lookup" Type Function
Replies
12
Views
326
arnelgp
arnelgp
X
When was my Worker's Last Day Off?
Replies
3
Views
383
MarkK
MarkK
O
Adding days to Now() but with specific times
2
Replies
22
Views
495
Gasman
Gasman
W
Forecasting a date based on working days.
Replies
2
Views
217
Gasman
Gasman
oxicottin
Getting current Fridays date
Replies
11
Views
441
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom