Working Days Module (1 Viewer)

M

mba_110

Registered User.
Local time
Today, 01:02
Joined
Jan 20, 2015
Messages
280
Dear All,

I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.

So, i select the module for Access 2010 but also not finish with function i hope i could able to finalize it with you guys help.

Below is coding for function...please help to complete and kindly correct the wrong coding.

Table name is "LeaveSettlement" and column name is "Total_Wdays" please also tell me what field type i have to select for result column.

_________________________________________________________

Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error

' The number of weekend days per week.
Const LeaveSettlement As Integer = 2

' The number of days inclusive.
Dim varDays As Variant

' The number of weekend days.
Dim varWeekendDays As Variant

' Temporary storage for datetime.
Dim dtmX As Date

' If the end date is earlier, swap the dates.
If Leave_End < Leave_Start Then
dtmX = Leave_Start
Leave_Start = Leave_End
Leave_End = dtmX
End If

' Calculate the number of days inclusive (+ 1 is to add back Leave_Start).
varDays = DateDiff(Interval:="d", _
date1:=Leave_Start, _
date2:=Leave_End) + 1

' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=Leave_Start, _
date2:=Leave_End) _
* LeaveSettlement) _
+ IIf(DatePart(Interval:="w", _
Date:=Leave_Start) = vbFriday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=Leave_End) = vbSaturday, 1, 0)

' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
Exit Function

Weekdays_Error:
Weekdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function

_________________________________________________________

Thanks & regards,

MBA
 
M

mba_110

Registered User.
Local time
Today, 01:02
Joined
Jan 20, 2015
Messages
280
Code: 
If DateValue(Me.Combo0.Column(2)) <= date Than
MsgBox “Password expired”
Else
MsgBox “password OK”
End if

===============================================
Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error

' The number of weekend days per week.
Const LeaveSettlement As Integer = 2

' The number of days inclusive.
Dim varDays As Variant

' The number of weekend days.
Dim varWeekendDays As Variant

' Temporary storage for datetime.
Dim dtmX As Date

' If the end date is earlier, swap the dates.
If Leave_End < Leave_Start Then
dtmX = Leave_Start
Leave_Start = Leave_End
Leave_End = dtmX
End If

' Calculate the number of days inclusive (+ 1 is to add back Leave_Start).
varDays = DateDiff(Interval:="d", _
date1:=Leave_Start, _
date2:=Leave_End) + 1

' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=Leave_Start, _
date2:=Leave_End) _
* LeaveSettlement) _
+ IIf(DatePart(Interval:="w", _
Date:=Leave_Start) = vbFriday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=Leave_End) = vbSaturday, 1, 0)

' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
Exit Function

Weekdays_Error:
Weekdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function
==============================================
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 09:02
Joined
Nov 30, 2011
Messages
8,494
+1 for try.

Code: 
Public Function LeaveSettlement(    ByRef Leave_Start As Date, _
                                    ByRef Leave_End As Date _
                                )   As Integer
[COLOR=Green]    ' Returns the number of weekdays in the period from Leave_Start
    ' to Leave_End inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Friday and
    ' do not total two per week in number, this function will
    ' require modification.[/COLOR]

On Error GoTo Weekdays_Error
[COLOR=Green]    ' The number of weekend days per week.[/COLOR]
    Const LeaveSettlement As Integer = 2

[COLOR=Green]    ' The number of days inclusive.[/COLOR]
    Dim varDays As Variant

[COLOR=Green]    ' The number of weekend days.[/COLOR]
    Dim varWeekendDays As Variant

[COLOR=Green]    ' Temporary storage for datetime.[/COLOR]
    Dim dtmX As Date

[COLOR=Green]    ' If the end date is earlier, swap the dates.[/COLOR]
    If Leave_End < Leave_Start Then
        dtmX = Leave_Start
        Leave_Start = Leave_End
        Leave_End = dtmX
    End If

[COLOR=Green]    ' Calculate the number of days inclusive (+ 1 is to add back Leave_Start).[/COLOR]
    varDays = DateDiff(Interval:="d", _
                        date1:=Leave_Start, _
                        date2:=Leave_End) + 1

[COLOR=Green]    ' Calculate the number of weekend days.[/COLOR]
    varWeekendDays = (DateDiff(Interval:="ww", _
                                date1:=Leave_Start, _
                                date2:=Leave_End) _
                                * LeaveSettlement) _
                                + IIf(DatePart(Interval:="w", _
                                Date:=Leave_Start) = vbFriday, 1, 0) _
                                + IIf(DatePart(Interval:="w", _
                                Date:=Leave_End) = vbSaturday, 1, 0)

[COLOR=Green]    ' Calculate the number of weekdays.[/COLOR]
    Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
    Exit Function

Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
            vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function
 
M

mba_110

Registered User.
Local time
Today, 01:02
Joined
Jan 20, 2015
Messages
280
I did try this and copy paste the above coding but not working.

The result column "Total_Wdays" in table "LeaveSettlement" is still blank please advise am i missed anything to run or to save? in module or field type etc?

thanks.
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 09:02
Joined
Nov 30, 2011
Messages
8,494
I did not change anything. I never mentioned in my post, the code will work. I only formatted the code, so it is easier to read.

So let us start again. Forget the code you have in simple words, explain your requirement.
Example said:
Sam enters two dates, and wants to find the number of days between the two. The logic should work in a way it should not consider Friday as a working day.
Click to expand...
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 10:02
Joined
Aug 11, 2003
Messages
11,695
A function should never be storing a value into a table?

You call this fuction from a query and have it show the result, NEVER store calculated values in a table !
 
M

mba_110

Registered User.
Local time
Today, 01:02
Joined
Jan 20, 2015
Messages
280
Dear Pr2,

Many thanks for your quick reply.

I will try it in query and let you know the situation.

This is the post i have forwarded in Mrexcel forum also but not replied by up to now and i need this to be done as early as possible.

I need the following diffdate expression formula in MS access 2010 this is to calculate total working days between two dates.

Actually i need two methods in my Access table to produce the result with total number of working days.

I have columns [Leave_Start] and [Leave_End] and result column is [Total_Wdays]

First Method
1. First method is to calculate number of working days between these [Leave_Start] and [Leave_End] dates but their is one twist during this calculation weekends should be Thursday & Fridays but from 29/06/13 the weekend should be Friday & Saturdays i know its same two days but it make difference if anyone went on holidays during/after these days.

Second Method
Second method is straight to calculate number of working days between these [Leave_Start] and [Leave_End] dates without any cut off or twist during this calculation weekends should be Friday & Saturdays.

Kindly provide the formula/coding in Access 2010 for above methods.

Thanks & regards,

MBA
 
You must log in or register to reply here.

Similar threads

Gasman
nth Day of month
Replies
4
Views
710
ebs17
E
M
Solved Need to count the number of a specific day between two dates in access vba
Replies
9
Views
1,028
BAntar
B
A
Solved Code cannot differentiate between regional dates
Replies
5
Views
535
Ajabu
A
Coxylaad
First and last day of week
Replies
6
Views
693
Coxylaad
Coxylaad
P
HELP-Run-Time Error "3048" - cannot open anymore databases following Microsoft Update.
Replies
2
Views
875
Minty
Minty

Users who are viewing this thread

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